CODEWITHSUNDEEP
sqlsql-server
user3012629
user3012629

Reputation: 655

How to select most recent values?

I have a logging table collecting values from many probes:

CREATE TABLE [Log]
(
  [LogID] int  IDENTITY (1, 1)   NOT NULL,
  [Minute] datetime  NOT NULL,
  [ProbeID] int   NOT NULL DEFAULT 0,
  [Value] FLOAT(24)  NOT NULL DEFAULT 0.0,

  CONSTRAINT Log_PK PRIMARY KEY([LogID])
)
GO

CREATE INDEX [Minute_ProbeID_Value] ON [Log]([Minute], [ProbeID], [Value])
GO

Typically, each probe generates a value every minute or so. Some example output:

LogID   Minute            ProbeID Value
======  ================  ======= =====
873875  2014-07-27 09:36  1972    24.4
873876  2014-07-27 09:36  2001    29.7
873877  2014-07-27 09:36  3781    19.8
873878  2014-07-27 09:36  1963    25.6
873879  2014-07-27 09:36  2002    22.9
873880  2014-07-27 09:36  1959    -30.1
873881  2014-07-27 09:36  2005    20.7
873882  2014-07-27 09:36  1234    23.8
873883  2014-07-27 09:36  1970    19.9
873884  2014-07-27 09:36  1991    22.4
873885  2014-07-27 09:37  1958    1.7
873886  2014-07-27 09:37  1962    21.3
873887  2014-07-27 09:37  1020    23.1
873888  2014-07-27 09:38  1972    24.1
873889  2014-07-27 09:38  3781    20.1
873890  2014-07-27 09:38  2001    30
873891  2014-07-27 09:38  2002    23.4
873892  2014-07-27 09:38  1963    26
873893  2014-07-27 09:38  2005    20.8
873894  2014-07-27 09:38  1234    23.7
873895  2014-07-27 09:38  1970    19.8
873896  2014-07-27 09:38  1991    22.7
873897  2014-07-27 09:39  1958    1.4
873898  2014-07-27 09:39  1962    22.1
873899  2014-07-27 09:39  1020    23.1

What is the most efficient way to get just the latest reading for each Probe?

e.g.of desired output (note: the "Value" is not e.g. a Max() or an Avg()):

LogID   Minute             ProbeID  Value
======  =================  =======  =====
873899  27-Jul-2014 09:39  1020     3.1
873894  27-Jul-2014 09:38  1234     23.7
873897  27-Jul-2014 09:39  1958     1.4
873880  27-Jul-2014 09:36  1959     -30.1
873898  27-Jul-2014 09:39  1962     22.1
873892  27-Jul-2014 09:38  1963     26
873895  27-Jul-2014 09:38  1970     19.8
873888  27-Jul-2014 09:38  1972     24.1
873896  27-Jul-2014 09:38  1991     22.7
873890  27-Jul-2014 09:38  2001     30
873891  27-Jul-2014 09:38  2002     23.4
873893  27-Jul-2014 09:38  2005     20.8
873889  27-Jul-2014 09:38  3781     20.1

Upvotes: 3

Views: 84

Answers (4)

Brian DeMilia
Brian DeMilia

Reputation: 13248

This is another approach

select *
  from log l
 where minute =
       (select max(x.minute) from log x where x.probeid = l.probeid)

You can compare the execution plan w/ a fiddle - http://sqlfiddle.com/#!3/1d3ff/3/0

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271141

Your question is: "What is the most efficient way to get just the latest reading for each Probe?"

To really answer this question, you test to test different solutions. I would generally go with the row_number() method suggested by @jyparask. However, the following might have better performance:

select l.*
from log l
where not exists (select 1
                  from log l2
                  where l2.probeid = l.probeid and
                        l2.minute > l.minute
                 );

For performance, you want an index on log(probeid, minute).

Although not exactly your problem, here is an example of where not exists performs better than other methods on SQL Server.

Upvotes: 1

Gidil
Gidil

Reputation: 4137

Try this:

SELECT T1.* 
FROM   Log T1 
       INNER JOIN (SELECT Max(Minute) Minute, 
                          ProbeID 
                   FROM   Log 
                   GROUP  BY ProbeID)T2 
               ON T1.ProbeID = T2.ProbeID 
                  AND T1.Minute = T2.Minute

You can play around with it on SQL Fiddle

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

;WITH MyCTE AS
(
    SELECT LogID,
           Minute,
           ProbeID,
           Value,
           ROW_NUMBER() OVER(PARTITION BY ProbeID ORDER BY Minute DESC) AS rn
    FROM   LOG
)
SELECT LogID,
       Minute,
       ProbeID,
       Value 
FROM   MyCTE
WHERE  rn = 1

Upvotes: 0

Related Questions