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
deebollAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
> .. I want one per Product_Code

You didn't mention that.
Anyway, use a subquery to look up the ID for each Product_Code:

SELECT
    DateSerial(Year(Contract_Month), Month(Contract_Month), 1) AS
  ContractMonth,
  TT_Prices.Product_code,
  TT_Prices.Price
FROM
  TT_Prices
GROUP BY
  Contract_Month,
  TT_Prices.Product_code,
  TT_Prices.Price
HAVING
  (TT_Prices.ID IN
    (SELECT TOP 1
      T.ID
    FROM
      TT_Prices AS T
    WHERE
      TT_Prices.Product_code = T.Product_code
    ORDER BY
      Date_Stamp DESC,
      ID DESC;))

/gustav
0
 
OP_ZaharinCommented:
- 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

0
 
OP_ZaharinCommented:
- if you want to get the latest date from Date _Stamp column then use MAX(Date _Stamp)
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Gustav BrockCIOCommented:
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
0
 
deebollAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
As CDate is mentioned it must be Access.
Tables could be linked ...

/gustav
0
 
Gustav BrockCIOCommented:
Did you find out?

/gustav
0
 
deebollAuthor Commented:
Sorry guys - been hibernating - yes, its a access question.
0
 
Gustav BrockCIOCommented:
> yes, its a access question.

Of course. So much chat about this.

How about the solution left here three weeks ago?

/gustav
0
 
deebollAuthor Commented:
This didn't work for me - but it definitely gave me ideas to build another query
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.