CODEWITHSUNDEEP
sqlsql-serverrdbms
Aditya
Aditya

Reputation: 13

SQL Server Query to get latest items

I have the following information in a table. Assume that we have various fruits where each crate of fruit can have one of the three quality level (Good, bad, and average).

I want to query the table to get the latest quality status of a fruit.

Fruit Quality Timestamp
Orange Good Nov 10, 11:20 AM
Apple Bad Nov 10, 11:10 AM
Banana Good Nov 10, 10 AM
Apple Average Nov 10, 8 AM
Grapes Bad Nov 10, 7 AM
Grapes Average Nov 10, 6:45 AM
Apple Good Nov 10, 6:20 AM
Banana Good Nov 10, 5 AM
Orange Average Nov 9, 11 AM
Orange Bad Nov 9, 10:20 AM
Apple Good Nov 9, 8:20 AM
Grapes Good Nov 9, 5 AM
Mango Bad Nov 9, 4 AM


Result of query should be:
Orange Good
Apple Bad
Banana Good
Grapes Bad
Mango Bad

I am a newbie and struggling with this query. Any help would be appreciated. Thanks!

Upvotes: 1

Views: 174

Answers (2)

John Woo
John Woo

Reputation: 263733

WITH fruitStatus
AS
(
    SELECT fruit, quality, [timestamp],
            ROW_NUMBER() OVER (Partition BY Fruit ORDER BY [TimeStamp] DESC) rn
    FROM    tableName
)
SELECT fruit, quality
FROM fruitStatus
WHERE rn = 1

Upvotes: 2

Rob Paller
Rob Paller

Reputation: 7786

Have you considered using a window aggregate function along with the QUALIFY clause?

SELECT Fruit
     , Grade
  FROM MyTable
 QUALIFY MAX(Timestamp) OVER (PARTITION BY Fruit) = Timestamp

You should also be able to accomplish it this way:

SELECT Fruit
     , Grade
  FROM MyTable T1
 INNER JOIN
      (SELECT Fruit
            , MAX(Timestamp) AS MaxTimeStamp
         FROM MyTable
        GROUP BY 1) DT1
    ON T1.Fruit = DT1.Fruit
   AND T1.TimeStamp = DT1.MaxTimeStamp;

Upvotes: 2

Related Questions