...

View Full Version : Distinct reference with min time SQL query



martinjsmith
12-06-2006, 12:11 PM
Table TVEMCQM1
QM_QUOTE_REF
QM_QUOTE_TIMESTAMP
QM_PRODUCT_CODE

QM_QUOTE_REF QM_QUOTE_TIMESTAMP QM_PRODUCT_CODE
EQS10000033 2006-12-05-15.21.13.200000 DEF
EQS10000033 2006-12-04-15.16.12.960000 ABC
EQS10000041 2006-12-05-09.04.26.840000 123
EQS10000041 2006-12-05-10.04.27.840000 456
EQS10000041 2006-12-05-11.04.28.840000 789

I need a query that produces a row for each unique QM_QUOTE_REF which has the minimum/earliest QM_QUOTE_TIMESTAMP along with whatever QM_PRODUCT_CODE value is applies to the row with this minimum/earliest QM_QUOTE_TIMESTAMP.

e.g. From the above table the results should be:

QM_QUOTE_REF QM_QUOTE_TIMESTAMP QM_PRODUCT_CODE
EQS10000033 2006-12-04-15.16.12.960000 ABC
EQS10000041 2006-12-05-09.04.26.840000 123

Can anyone suggest a simple/efficient way to do this?

Many thanks!

CFMaBiSmAd
12-06-2006, 01:21 PM
This will work -

SELECT QM_QUOTE_REF, QM_QUOTE_TIMESTAMP, QM_PRODUCT_CODE
FROM TVEMCQM1 s1
WHERE QM_QUOTE_TIMESTAMP=(SELECT MIN(s2.QM_QUOTE_TIMESTAMP)
FROM TVEMCQM1 s2
WHERE s1.QM_QUOTE_REF = s2.QM_QUOTE_REF)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum