asked on

SQL to retrieve the record with the latest timestamp

Hello -

I am trying to retrieve a record with the lates timestamp from a table. Can someone help me with the SQL?

Here' the basic SQL to which the timestamp piece needs to be added
SELECT cdate('01-' + Format(TT_Prices.Contract_Month, 'mmm-yy')) as contractmonth, TT_Prices.Product_code, TT_Prices.Price
FROM TT_Prices
where 1=1
group by Contract_Month,Product_code,Price

Sample data
ID Contract_Month      Product_code Data_Type Price      Date_Stamp
306      Jun-11      CL        $110.06        4/15/2011 15:21
576      Jun-11      CL        $110.01        4/15/2011 15:54
1103      Jun-11      CL        $112.77        4/28/2011 16:11
1545      Jun-11      CL        $113.13        5/2/2011 14:58

OP_Zaharin

- is this ACCESS database? use MAX():

SELECT  MAX(cdate('01-' +  Format(TT_Prices.Contract_Month, 'mmm-yy'))) as contractmonth, TT_Prices.Product_code, TT_Prices.Price
FROM TT_Prices 
WHERE 1=1
GROUP BY Contract_Month,Product_code,Price

Open in new window

OP_Zaharin

- if you want to get the latest date from Date _Stamp column then use MAX(Date _Stamp)

Gustav Brock

You can't do it with an aggregation query.
Just pick one record:

SELECT TOP 1
DateSerial(Year(Contract_Month), Month(Contract_Month), 1) AS
ContractMonth,
TT_Prices.Product_code,
TT_Prices.Price
FROM
TT_Prices
ORDER BY
Date_Stamp DESC,
ID DESC;

/gustav

deeboll

ASKER

Yeah Max () didn't work . SELECT Top 1 worked. However, it selects the top 1 from the entire set of data. i however have multiple product_code s - not just CL. And I want one per Product_Code. Can you please help?

Sample data
contractmonth      Product_code      Price      Date_Stamp      ID
6/1/2011      RB      $3.34      5/2/2011 2:58:02 PM      1548
6/1/2011      CO      $124.78      5/2/2011 2:58:02 PM      1546
6/1/2011      CL      $113.13      5/2/2011 2:58:01 PM      1545
6/1/2011      RB      $3.37      4/28/2011 4:12:04 PM      1106
6/1/2011      CO      $124.79      4/28/2011 4:12:00 PM      1104
6/1/2011      CL      $112.77      4/28/2011 4:11:58 PM      1103
6/1/2011      RB      $3.25      4/15/2011 3:21:44 PM      309
6/1/2011      CO      $123.46      4/15/2011 3:21:43 PM      307
6/1/2011      CL      $110.06      4/15/2011 3:21:43 PM      306
6/1/2011      RB      $3.26      4/15/2011 3:54:31 PM      579
6/1/2011      CO      $123.58      4/15/2011 3:54:31 PM      577
6/1/2011      CL      $110.01      4/15/2011 3:54:31 PM      576

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

As CDate is mentioned it must be Access.
Tables could be linked ...

/gustav

Gustav Brock

Did you find out?

/gustav

deeboll

ASKER