Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jul 2002
    Location
    Glasgow, Scotland
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Distinct reference with min time SQL query

    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!

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,146
    Thanks
    2
    Thanked 333 Times in 325 Posts
    This will work -
    Code:
    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)
    Last edited by CFMaBiSmAd; 12-06-2006 at 02:24 PM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •