Comment returnner la dernière ligne écrite pour la journée?

bien sûr, cela fonctionnera avec MAX

puisque vous n'avez pas fourni d'instructions DDL et DML, j'ai utilisé quelque chose que j'avais

lance ça

  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 

sortie

 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 

N'est-ce pas impoli de supprimer votre ancien message sans avertissement parce que les exigences ont changé? Tu ne peux pas le fermer pour que les gens ne se requestnt pas ce qui s'est passé?

En tout cas, voici la réponse mise à jour:

 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 

Résultats

 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') 

Pour tous les jours, essayez ceci:

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

pour un jour donné, faites ceci:

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

Vous pouvez avoir une colonne calculée ou une autre colonne qui stocke simplement la date. Après cela, la requête sera sortingviale.

Cela fonctionnerait-il dans SqlServer2005?

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

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

Quelque chose comme ça, peut-être? Pas testé, du haut de ma tête.

Essaye ça…

 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 

select top 1 *
de foo
où CreatedTime entre @today et @tomorrow
ordre par CreatedTime desc

(@today et @tomorrow étant des espaces réservés pour la plage de dates que vous voulez.)

 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