How to Select Oldest Date in SQL?

Question

I have this sql statement:

SELECT TOP (5) PartNumber,SerialNumber,MIN(WIP_CompletionDate) as dates
FROM dbo.FG_FILLIN where 
             Status='FG-FRESH'
             and WIP_Status<>'CMPL01' 
             and PartNumber='P02-070161-10211-C100'
            GROUP BY PartNumber,WIP_CompletionDate,SerialNumber

Result:

PartNumber                  SerialNumber               dates
P02-070161-10211-C100   21524100046CA8001806    2010-08-08 06:59:23.183
P02-070161-10211-C100   21524100046CA8001807    2010-08-07 16:26:19.477
P02-070161-10211-C100   21524100046CA8001808    2010-08-07 16:30:20.990
P02-070161-10211-C100   21524100046CA8001810    2010-08-14 13:12:58.827
P02-070161-10211-C100   21524100046CA8001811    2010-08-09 06:58:01.263

Now if i select top (2) this is my result.

P02-070161-10211-C100   21524100046CA8001806    2010-08-08 06:59:23.183
P02-070161-10211-C100   21524100046CA8001807    2010-08-07 16:26:19.477

But Supposed to be i the result will be something like this.

P02-070161-10211-C100 21524100046CA8001808  2010-08-07 16:26:19.477
P02-070161-10211-C100 21524100046CA8001808  2010-08-07 16:30:20.990

Any Suggestions? Thanks in Regards

Answer 1

Try adding 'ORDER BY WIP_CompletionDate ASC' to your query like so:

SELECT TOP (5) PartNumber,SerialNumber, WIP_CompletionDate 
FROM dbo.FG_FILLIN 
WHERE Status='FG-FRESH' AND WIP_Status<>'CMPL01' AND PartNumber='P02-070161-10211-C100' 
GROUP BY PartNumber, WIP_CompletionDate, SerialNumber 
ORDER BY WIP_CompletionDate ASC;

Answer 2

Add an ORDER BY WIP_CompletionDate ASC clause to the end of your query. It would order the query by the dates, and you select the top two, thus being the oldest ones.

Answer 3

The other answers are almost right, the exact way would be:

SELECT TOP (5) PartNumber,SerialNumber,MIN(WIP_CompletionDate) as dates
FROM dbo.FG_FILLIN
WHERE Status='FG-FRESH' and WIP_Status<>'CMPL01' and PartNumber='P02-070161-10211-C100'
GROUP BY PartNumber,WIP_CompletionDate,SerialNumber
ORDER BY dates

So ORDER BY dates, and no need to explicitly specify that it's ascending.

Answer 4

Use Order by dates Asc with your query,