How to return the last line written in a day?

Question

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

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

Below is an example of the data and the result that I expect. I need the latest data created per day. Therefore, MAX (CreatedDate) will not work. Thank you

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

of course he will work with MAX

DDL DML, -,

    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

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 2

, ? , , ?

:

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

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 3

:

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

, :

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

Answer 4

, , . .

Answer 5

SqlServer2005?

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

Answer 6

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

- , ? , .

Answer 7

...

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 8

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

(@today and @tomorrow are 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