Selecting rows by earliest date
-
I have a table "tbl_order" with to the fields "customer_number" and "date". there can be multiple rows with the same customer_number, but the date fields are allways unique. these or potential rows in my table: cust_no | date | item --------------------------- 21 2006-01-06 shovel 21 2006-03-05 axe 21 2006-05-05 widget 45 2006-05-01 fish tank 45 2006-05-02 ... For every cust_no i want to retreive only the row with the earliest date. How can this be done? what i want is this: ------------------------ 21 2006-01-06 shovel 45 2006-05-01 fish tank Any help would be greatly appreciated / Stefan
-
I have a table "tbl_order" with to the fields "customer_number" and "date". there can be multiple rows with the same customer_number, but the date fields are allways unique. these or potential rows in my table: cust_no | date | item --------------------------- 21 2006-01-06 shovel 21 2006-03-05 axe 21 2006-05-05 widget 45 2006-05-01 fish tank 45 2006-05-02 ... For every cust_no i want to retreive only the row with the earliest date. How can this be done? what i want is this: ------------------------ 21 2006-01-06 shovel 45 2006-05-01 fish tank Any help would be greatly appreciated / Stefan
SELECT q.cust_no, earliest_date, item
FROM tbl_order as t
INNER JOIN
(
SELECT cust_no, MIN(date) as earliest_date
FROM tbl_order
GROUP BY cust_no
) AS q ON t.cust_no = q.cust_no AND t.date = q.earliest_date
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog