CODEWITHSUNDEEP
sqlsql-server
DDan
DDan

Reputation: 8276

Query max time (latest) by id SQL Server

I have a similar table:

ID     | Name    | Time
-------|---------|-----------------------------
111    | AAA     | 2015-11-05 15:39:24.000
222    | BBB     | 2015-11-04 11:29:11.000
111    | AAA     | 2015-11-02 13:12:10.000
333    | CCC     | 2015-11-05 15:39:24.000
111    | AAA     | 2015-11-01 15:39:24.000

I would like to get the latest Time for every ID. My output would look something like this:

ID     | Name    | Time
-------|---------|-----------------------------
111    | AAA     | 2015-11-05 15:39:24.000
222    | BBB     | 2015-11-04 11:29:11.000
333    | CCC     | 2015-11-05 15:39:24.000

Please help me formulate the query!

Can I use max(Time) to get latest Time? Do I need to JOIN the table with itself to be able to list max(Time) for all IDs?

Upvotes: 1

Views: 76

Answers (4)

Tim Schmelter
Tim Schmelter

Reputation: 460048

If there are more columns you want to keep you could use a CTE with ROW_NUMBER function:

WITH CTE AS
(
    SELECT Id, Name, Time, OtherColumns...,
           RN = ROW_NUMBER() OVER (PARTITION BY ID Order By Time DESC)
    FROM dbo.TableName
)
SELECT Id, Name, Time, OtherColumns...
FROM CTE
WHERE RN = 1

If it's possible that there are mutliple rows with the same max-time per ID and you want all of them instead of one arbitrary replace ROW_NUMBER with DENSE_RANK. Ranking Functions

Upvotes: 4

A. Greensmith
A. Greensmith

Reputation: 375

Your question does not say that you want to get your output based on NAME, just ID. Therefore the previous answers are technically wrong. You should be using:

SELECT ID, AGGREGATEFUNCTION(Name), MAX(Time) AS [Time]
FROM Your_Table
GROUP BY ID

This means if your names differ then they will not create a separate group.

It also meets exactly your requirement.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You can use MAX function:

SELECT ID, Name, MAX([Time]) AS [Time]
FROM your_table
GROUP BY ID, Name ;

LiveDemo

Output:

╔═════╦══════╦═════════════════════╗
║ ID  ║ Name ║        Time         ║
╠═════╬══════╬═════════════════════╣
║ 111 ║ AAA  ║ 2015-11-05 15:39:24 ║
║ 222 ║ BBB  ║ 2015-11-04 11:29:11 ║
║ 333 ║ CCC  ║ 2015-11-05 15:39:24 ║
╚═════╩══════╩═════════════════════╝

There is one catch though, data you provided has the same ID/NAME pairs. With different data you will get:

INSERT INTO #Your_Table(ID,Name,Time) VALUES (111,'AAA','2015-11-05 15:39:24.000');
INSERT INTO #Your_Table(ID,Name,Time) VALUES (222,'BBB','2015-11-04 11:29:11.000');
INSERT INTO #Your_Table(ID,Name,Time) VALUES (111,'AAA','2015-11-02 13:12:10.000');
INSERT INTO #Your_Table(ID,Name,Time) VALUES (333,'CCC','2015-11-05 15:39:24.000');
INSERT INTO #Your_Table(ID,Name,Time) VALUES (111,'DDD','2015-11-01 15:39:24.000');

Output:

╔═════╦══════╦═════════════════════╗
║ ID  ║ Name ║        Time         ║
╠═════╬══════╬═════════════════════╣
║ 111 ║ AAA  ║ 2015-11-05 15:39:24 ║
║ 222 ║ BBB  ║ 2015-11-04 11:29:11 ║
║ 333 ║ CCC  ║ 2015-11-05 15:39:24 ║
║ 111 ║ DDD  ║ 2015-11-01 15:39:24 ║
╚═════╩══════╩═════════════════════╝

If you want actual row and not result of grouping use like here:

WITH cte AS
(
  SELECT ID, MAX([Time]) AS [Time]
  FROM #your_table
  GROUP BY ID
)
SELECT t.*
FROM #your_table t
JOIN cte c
  ON c.ID = t.ID
 AND c.[Time] = t.[Time]

LiveDemo2

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Use MAX function to find the latest time.

Query

SELECT ID, Name,
     MAX(Time) AS [Time]
FROM your_table_name
GROUP BY ID, Name;

Upvotes: 2

Related Questions