How to return the latest row written for the day?

Question

I have a table with a FooId and a CreatedTime column. During the day, multiple rows can be inserted for the same FooId. The CreatedTime column represent the time at the moment of the inserting.

I would like a query which will return me the latest row for a given day (e.g. 2000-01-01). Is there a way to write a query which will do that with SQL Server 2005?

Below is an example of the data and the result that I am expecting. I would like the latest data created for the day. So the MAX(CreatedDate) won't work. Thanks!

FooId   Data  CreatedTime
---------------------------
1       A     2000/01/01 12:00:00
1       B     2000/01/01 12:12:00
1       C     2000/01/01 12:25:00
2       A     2000/01/01 12:00:00
2       B     2000/01/01 12:26:00
3       A     2000/01/01 12:00:00

Result

FooId   Data  CreatedTime
---------------------------
1       C     2000/01/01 12:25:00
2       B     2000/01/01 12:26:00
3       A     2000/01/01 12:00:00

Answer 1

You might want to have a computed column or another column that just stores the date. After this the query will be trivial.

Answer 2

Would this work in SqlServer2005?

SELECT FoodId, Data, CreatedTime WHERE CreatedTime=max(CreatedTime) GROUP BY FoodId, day(CreatedTime)

Answer 3

Isn't it kind of rude to delete your old post without warning because the requirements changed? Can't you just close it so people don't wonder what happened?

In any case, here is the updated answer:

SELECT Foo.* 
FROM Foo
JOIN (
  SELECT FooId, MAX(CreatedTime) 
  FROM Foo Q
  -- Only change the dates in the next line.
  WHERE Q.CreatedTime >= '20000101' AND Q.CreatedTime < '20000102'
  GROUP BY Q.FooId, DATEADD(day, DATEDIFF(day, '19000101', Q.CreatedTime), '19000101')
) Q2 (FooID, CreatedTime) ON Q2.FooID = Foo.FooID AND Q2.CreatedTime = Foo.CreatedTime
ORDER BY FooID

Results

FooId   Data CreatedTime
1   C 2000-01-02 12:25:00.000
2   B 2000-01-02 12:26:00.000
3   A 2000-01-02 12:00:00.000

DDL

CREATE TABLE Foo (FooId int NOT NULL, Data varchar(10), CreatedTime datetime NOT NULL)

INSERT INTO Foo VALUES (1, 'A', '2000-01-01 12:00:00')
INSERT INTO Foo VALUES (1, 'B', '2000-01-01 12:12:00')
INSERT INTO Foo VALUES (1, 'C', '2000-01-01 12:25:00')
INSERT INTO Foo VALUES (2, 'A', '2000-01-01 12:00:00')
INSERT INTO Foo VALUES (2, 'B', '2000-01-01 12:26:00')
INSERT INTO Foo VALUES (3, 'A', '2000-01-01 12:00:00')

INSERT INTO Foo VALUES (1, 'A', '2000-01-02 12:00:00')
INSERT INTO Foo VALUES (1, 'B', '2000-01-02 12:12:00')
INSERT INTO Foo VALUES (1, 'C', '2000-01-02 12:25:00')
INSERT INTO Foo VALUES (2, 'A', '2000-01-02 12:00:00')
INSERT INTO Foo VALUES (2, 'B', '2000-01-02 12:26:00')
INSERT INTO Foo VALUES (3, 'A', '2000-01-02 12:00:00')

Answer 4

select data 
 from fooTable 
 where CreatedTime between <beginning of day> and <end of day> 
 order by CreatedTime DESC 
 limit 1

Something like that, perhaps? Not tested, off the top of my head.

Answer 5

Try this...

select f1.FooID, f1.Data, f1.CreatedTime
from Foo f1
inner join
(
    select f2.FooID, max(f2.CreatedTime) as CreatedTime
    from Foo f2
    where f2.CreatedTime >= '2000-01-01' and f2.CreatedTime < '2000-01-02'
    group by f2.FooID
) f3
on f3.FooID = f1.FooID
and f3.CreatedTime = f1.CreatedTime
order by 1, 2, 3

Answer 6

of course it will work with MAX

since you didn't provide DDL and DML statements I used something I had laying around

run this

    CREATE TABLE #MaxVal(id INT,VALUE varchar(10),SomeDate DATETIME)
    INSERT #MaxVal VALUES(1,'a','2009-02-10 14:48:45.143')
    INSERT #MaxVal VALUES(1,'b','2009-02-10 13:48:45.143')
    INSERT #MaxVal VALUES(1,'c','2009-02-10 11:48:45.143')
    INSERT #MaxVal VALUES(2,'d','2009-02-10 11:48:45.143')
    INSERT #MaxVal VALUES(2,'e','2009-02-10 12:48:45.143')
    INSERT #MaxVal VALUES(2,'f','2009-02-10 13:48:45.143')
    INSERT #MaxVal VALUES(3,'g','2009-02-10 11:48:45.143')
    INSERT #MaxVal VALUES(3,'h','2009-02-10 14:48:45.143')




SELECT t.* FROM(
    SELECT id,MAX(SomeDate) AS MaxValue
    FROM #MaxVal
    WHERE SomeDate >='2009-02-10'
        AND SomeDate < '2009-02-11'
    GROUP BY id) x
    JOIN #MaxVal t ON x.id =t.id
    AND x.MaxValue =t.SomeDate

output

id  VALUE SomeDate
3   h 2009-02-10 14:48:45.143
2   f 2009-02-10 13:48:45.143
1   a 2009-02-10 14:48:45.143

Answer 7

For all days, try this:

SELECT FooId, Max(CreatedTime)
FROM Foo
GROUP BY FooId, cast(CreatedTime as int)

for a given day, do this:

SELECT FooId, Max(CreatedTime)
FROM Foo
WHERE CreatedTime >= '2000-01-01' and CreatedTime < '2000-01-02'
GROUP BY FooId, cast(CreatedTime as int)

Answer 8

select top 1 *
from foo
where CreatedTime between @today and @tomorrow
order by CreatedTime desc

(@today and @tomorrow being placeholders for the date range you want.)

Answer 9

select x.FooId, x.Data, x.CreatedTime
from (select FooId,
             Data,
             CreatedTime,
             row_number() over (partition by FooId
                                order by CreatedTime desc) as rn
       from Foo
       where CreatedTime >= ...
          and CreatedTime < ...) as x
where x.rn = 1
order by FooId