CODEWITHSUNDEEP
sql-server
U2 Pas
U2 Pas

Reputation: 13

sql sub query with max(datetime)

I have a table dbo.counters with columns idx, idx_device, DateTime, Data1, Data2 etc.

I need to have from each idx_device one complete row which is closest to a specific DateTime, but not higher than.

I got already following formula, but this does not output all columns from table:

select max(DateTime), idx_device from dbo.counters 
    WHERE DateTime <= '02/04/12 23:59:59.995' 
    group by idx_device

guess should be something small, but I hang... thanks

Upvotes: 0

Views: 1496

Answers (3)

Malk
Malk

Reputation: 11983

Try this. 

DECLARE @CompareDate Datetime
SET @CompareDate =  '02/04/12 23:59:59.995'

;WITH c AS 
(
    SELECT *, rowrank = row_number() over(partition by idx_device order by DateTime desc)
    FROM dbo.Counters
    WHERE DateTime < @CompareDate
)
SELECT * FROM c WHERE rowrank = 1

Upvotes: 0

Cristian Lupascu
Cristian Lupascu

Reputation: 40526

Here is a solution using Common Table Expressions:

with cte as
    (select 
        *, 
        row_number() 
        over (partition by idx_device order by DateTime desc) as rowno
    from dbo.Counters
    where DateTime < '2012-02-04')
select * from cte where rowno = 1

Upvotes: 2

dash
dash

Reputation: 91480

The following should give you what you want:

SELECT * 
FROM    dbo.Counters C
INNER JOIN
      (SELECT idx_device, 
              MAX(DateTime) AS MaxDateTime
      FROM    dbo.Counters
      WHERE  DateTime < '20120205'
      GROUP BY idx_device) AS MaxCounters 
        ON C.idx_device = MaxCounters.idx_device AND C.DateTIme = MaxCounters.MaxDateTime

You are right in that you need to group your records by device id to get the max date time. Once we have those, we join back onto the parent table to retrieve the records with the same device id and also have that datetime.

Note that if you have multiple device id combos with the same time, you might want to consider using DISTINCT.

Upvotes: 0

Related Questions