CODEWITHSUNDEEP
sqlsql-servert-sql
Daniel
Daniel

Reputation: 35734

Getting a single row based on unique column

I think this is mostly a terminology issue, where I'm having a hard time articulating a problem.

I've got a table with a couple columns that manage some historical log data. The two columns I'm interested in are timestamp(or Id, as the id is generated sequentially) and terminalID.

I'd like to supply a list of terminal ids and find only the latest data, that is highest id or timestamp per terminalID

Ended up using group solution as @Danny suggested, and the other solution he referenced

I found the time difference to be quite noticeable, so I'm posting both results here for anyone's FYI.

S1:

SELECT UR.* FROM(
SELECT TerminalID, MAX(ID) as lID 
    FROM dbo.Results
    WHERE TerminalID in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)
    GROUP BY TerminalID
) GT left join dbo.Results UR on UR.id=lID

S2

SELECT *
FROM (
   SELECT TOP 100
      Row_Number() OVER (PARTITION BY terminalID ORDER BY Id DESC) AS [Row], *
   FROM dbo.Results
   WHERE TerminalID in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24)
   ORDER BY Id DESC
) a
WHERE a.row=1

the results were:

S1:

S2:

After adding the missing index to solution one (indexing ID only, as opposed to s2, where multiple columns needed an index), I got the query down to 15ms

Upvotes: 0

Views: 50

Answers (3)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

While not as obvious as using MAX with a GROUP BY, this can offer extra flexibility if you need to have more than one column determining which row or rows you want pulled back.

SELECT *
FROM (
   SELECT
      Row_Number() OVER (PARTITION BY terminalID ORDER BY Id DESC) AS [Row],
      [terminalID],[Id],[timestamp]
   FROM <TABLE>
   ORDER BY Id DESC
) a
WHERE a.row=1

Upvotes: 1

Danny
Danny

Reputation: 1758

I think you're on the right track with GROUP BY. Sounds like you want:

SELECT TerminalID, MAX(Timestamp) AS LastTimestamp
    FROM [Table_Name]
    WHERE TerminalID IN (.., .., .., ..)
    GROUP BY TerminalID

Upvotes: 2

rory.ap
rory.ap

Reputation: 35328

use the TOP keyword:

SELECT TOP 1 ID, terminalID FROM MyTable WHERE <your condition> ORDER BY <something that orders it like you need so that the correct top row is returned>.

Upvotes: 3

Related Questions