CODEWITHSUNDEEP
sqlsql-servert-sql
Geir
Geir

Reputation: 95

MSSQL - select latest occurrence of several rows in a table

So, I'm banging my head against the wall on this one... I have a table (of a few million rows) looking something like this:

ID  Time            Area    Name
1   2017-01-01 01:42    21  Test
1   2017-01-02 02:13    21  Test
1   2017-02-01 12:45    30  N/A
1   2017-03-12 02:23    41  N/A
2   2017-02-15 04:25    20  N/A

I want to run a query that selects the latest occurrence of area, of a certain ID; resulting in something like this:

ID  Time            Area    Name
1   2017-01-02 02:13    21  Test
1   2017-02-01 12:45    30  N/A
1   2017-03-12 02:23    41  N/A

select max(Time),
"of every area"
from TABLE
where ID = 1

If anyone can give me a clue, it would be awesome!

Upvotes: 3

Views: 86

Answers (4)

pblack
pblack

Reputation: 808

Or a more old school approach would be to use GROUP BY in a derived table

SELECT Test.* FROM dbo.testTable Test
INNER JOIN (
    SELECT Id, MAX(Time) Time, Area 
    FROM dbo.testTable
    GROUP BY Id, Area 
) TestMax ON TestMax.Id = Test.Id AND TestMax.Time = Test.Time AND 
TestMax.Area = Test.Area
ORDER BY Test.Id, Test.Area;

Upvotes: 1

Asif Nowaj
Asif Nowaj

Reputation: 356

lad2025 is correct.

To be more specific to your requirement for a specific ID, use

SELECT * FROM (
   SELECT *, ROW_NUMBER() OVER(PARTITION BY Area ORDER BY Time DESC) AS rn
   FROM table WHERE ID=1) sub
WHERE rn = 1

With CTE, you can use the same as 

;WITH cte AS (
  SELECT [ID], [Time], [Area], [Name],
     row_number() OVER(PARTITION BY [Area] ORDER BY [Time] desc) AS [rn]
  FROM [AreaTable] WHERE ID=1
)
SELECT [ID], [Time], [Area], [Name] FROM cte WHERE [rn] = 1

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82020

Yet another option (sans subquery and/or extra field)

Example

 Select Top 1 with Ties *
  From  YourTable
  Where ID =1
  Order By Row_Number() over (Partition By Area Order By [Time] Desc)

Returns

ID  Time                     Area   Name
1   2017-01-02 02:13:00.000  21     Test
1   2017-02-01 12:45:00.000  30     N/A
1   2017-03-12 02:23:00.000  41     N/A

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 176244

You could use ROW_NUMBER:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Area ORDER BY Time DESC) AS rn
  FROM table) sub
WHERE rn = 1

Upvotes: 3

Related Questions