CODEWITHSUNDEEP
sql-servert-sqlquery-optimization
Drakoo
Drakoo

Reputation: 327

T-SQL - get only latest row for selected condition

I have table with measurement with column SERIAL_NBR, DATE_TIME, VALUE. There is a lot of data so when I need them to get the last 48 hours for 2000 devices

Select * from MY_TABLE where [TIME]> = DATEADD (hh, -48, @TimeNow)

takes a very long time. Is there a way not to receive all the rows for each device, but only the latest entry? Would this speed up the query execution time?

Upvotes: 0

Views: 1448

Answers (4)

GandRalph
GandRalph

Reputation: 635

This will get you details of the latest record per serial number:

  Select t.SERIAL_NBR, q.FieldsYouWant
  from MY_TABLE t
  outer apply
  (
    selct top 1 t2.FieldsYouWant
    from MY_TABLE t2 
    where t2.SERIAL_NBR = t.SERIAL_NBR
    order by t2.[TIME] desc
  )q
  where t.[TIME]> = DATEADD (hh, -48, @TimeNow)

Also, worth sticking DATEADD (hh, -48, @TimeNow) into a variable rather than calculating inline.

Upvotes: 0

deroby
deroby

Reputation: 6002

You can simply create Common Table Expression that sorts and groups the entries and then pick the latest one from there.

;WITH numbered
   AS ( SELECT [SERIAL_NBR], [TIME], [VALUE], row_nr = ROW_NUMBER() OVER (PARTITION BY [SERIAL_NBR] ORDER BY [TIME] DESC)
          FROM MY_TABLE 
         WHERE [TIME]> = DATEADD (hh, -48, @TimeNow) )
SELECT [SERIAL_NBR], [TIME], [VALUE]
  FROM numbered
 WHERE row_nr = 1 -- we want the latest record only

Depending on the amount of data and the indexes available this might or might not be faster than Anthony Hancock's answer.

Similar to his answer you might also try the following: (from MSSQL's point of view, the below query and Anthony's query are pretty much identical and they'll probably end up with the same query plan)

SELECT [SERIAL_NBR] , [TIME], [VALUE]
  FROM MY_TABLE AS M
  JOIN (SELECT [SERIAL_NBR] , max_time = MAX([TIME])
          FROM MY_TABLE 
         GROUP BY [SERIAL_NBR]) AS L -- latest
    ON L.[SERIAL_NBR] = M.[SERIAL_NBR]
   AND L.max_time     = M.[TIME]
 WHERE M.DATE_TIME >= DATEADD(hh,-48,@TimeNow)

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Assuming that there is column named deviceId(change as per your needs), you can use top 1 with ties with window function row_number:

Select top 1 with ties *
from MY_TABLE 
where [TIME]> = DATEADD (hh, -48, @TimeNow)
Order by row_number() over (
                    partition by deviceId
                    order by Time desc
                );

Upvotes: 3

Anthony Hancock
Anthony Hancock

Reputation: 931

Your listed column values and your code don't quite match up so you'll probably have to change this code a little, but it sounds like for each SERIAL_NBR you want the record with the highest DATE_TIME in the last 48 hours. This should achieve that result for you.

SELECT SERIAL_NBR,DATE_TIME,VALUE
FROM MY_TABLE AS M
WHERE M.DATE_TIME >= DATEADD(hh,-48,@TimeNow)
    AND M.DATE_TIME = (SELECT MAX(_M.DATE_TIME) FROM MY_TABLE AS _M WHERE M.SERIAL_NBR = _M.SERIAL_NBR)

Upvotes: 1

Related Questions