SQL get the last date time record

Question

I m trying to get the last datetime record from a table that happens to store multiple status  My table looks like so    --------- ------------------------ -------   filename  Dates                    Status    --------- ------------------------ -------   abc txt   2012-02-14 12 04 45 397  Open      abc txt   2012-02-14 12 14 20 997  Closed    abc txt   2013-02-14 12 20 59 407  Open      dfg txt   2012-02-14 12 14 20 997  Closed    dfg txt   2013-02-14 12 20 59 407  Open      --------- ------------------------ -------    The results should be    --------- ------------------------ -------   filename  Dates                    Status    --------- ------------------------ -------   abc txt   2013-02-14 12 20 59 407  Open      dfg txt   2013-02-14 12 20 59 407  Open      --------- ------------------------ -------

User · Answer

SELECT TOP 1   FROM foo ORDER BY Dates DESC   Will return one result with the latest date   SELECT   FROM foo WHERE foo Dates    SELECT MAX Dates  FROM foo    Will return all results that have the same maximum date  to the milissecond   This is for SQL Server  I ll leave it up to you to use the DATEPART function if you want to use dates but not times

User · Answer

Considering that max dates  can be different for each filename  my solution    select filename  dates  status from yt a where a dates       select max dates      from yt b     where a filename   b filename       http   sqlfiddle com   18 fdf8d 1 0  HTH

User · Answer

Exact syntax will of course depend upon database  but something like   SELECT   FROM my table WHERE  filename  Dates  IN  SELECT filename  Max Dates  FROM my table GROUP BY filename    This will give you results exactly what you are asking for and displaying above   Fiddle  http   www sqlfiddle com   2 3af8a 1 0

User · Answer

this working   SELECT distinct filename  last value dates over  PARTITION BY filename ORDER BY filename posd  last value status over  PARTITION BY filename ORDER BY filename  poss FROM distemp dbo Shmy table

User · Answer

If you want one row for each filename  reflecting a specific states and listing the most recent date then this is your friend   select filename          status            max date   max  dates   from some table t group by filename   status having status     lt your-desired-status-here gt     Easy

User · Answer

Try this   SELECT filename Dates Status  FROM TableName  WHERE Dates In  SELECT MAX Dates  FROM TableName GROUP BY filename

User · Answer

select max dates  from yourTable group by dates having count status   gt  1

User · Answer

SELECT   FROM table WHERE Dates IN  SELECT max Dates  FROM table

[sql] SQL get the last date time record

Examples related to sql

Examples related to sql-server

Examples related to datetime

Examples related to sql-server-2000