• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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
0
deeboll
Asked:
deeboll
  • 5
  • 3
  • 2
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:
> .. 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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now