Tsql Get newest records from table

Question

Hi,

i have this table:

Location                      date                temp

1                             12-12-2009         19
1                             14-12-2009         21
1                             13-12-2009         17
2                             12-12-2009         18
2                             14-12-2009         16
2                             18-12-2009         12
2                             15-12-2009         14

now i want to select the latest (newest date) rows for all locations, so tin his example row 2 and row 7. How can i do this?

i can select the latest for 1 location with top 1 date for a single location, i tried grouping by location, date but then i miss the temp....

Kind regards, Michel

Answer 1

What about selecting the newest date in a Subselect like this:

select * from t where date = (select max(date) from t);

Answer 2

Have a look at this

DECLARE @Table TABLE(
     Location INT,
     Date DATETIME,
     Temp INT
)

INSERT INTO @Table (Location,Date,Temp) SELECT 1, '12 Dec 2009', 19
INSERT INTO @Table (Location,Date,Temp) SELECT 1, '14 Dec 2009', 21
INSERT INTO @Table (Location,Date,Temp) SELECT 1, '13 Dec 2009', 17

INSERT INTO @Table (Location,Date,Temp) SELECT 2, '12 Dec 2009', 18
INSERT INTO @Table (Location,Date,Temp) SELECT 2, '14 Dec 2009', 16
INSERT INTO @Table (Location,Date,Temp) SELECT 2, '18 Dec 2009', 12
INSERT INTO @Table (Location,Date,Temp) SELECT 2, '15 Dec 2009', 14


SELECT  t.*
FROM    @Table t INNER JOIN
     (
      SELECT Location,
        MAX(Date) MaxDate
      FROM @Table
      GROUP BY Location
     ) MaxDates ON t.Location = MaxDates.Location
        AND t.Date = MaxDates.MaxDate
ORDER BY 1

The only thing you might want to look out for is when the maxdat for a given location might ocuur more than once, the join will return more than one result for the location, date combination.

You might want to decide on how to select which of these results or all you want to return.

Answer 3

Try something like:

SELECT y.location, y.date, y.temp FROM yourTable y
INNER JOIN (
    SELECT location, MAX(date) AS latestDate
    FROM yourTable
    GROUP BY location
) tmp ON y.location = tmp.location AND y.date = tmp.latestDate
ORDER BY y.location

N.B. This assumes that each date is only recorded once against each location.

To understand how to derive this, remember you're dealing with sets, so start with the easy bit:

SELECT location, MAX(date)
FROM yourTable
GROUP BY location

This gets you the most recent date for each location. Then you want to extract the temperature for each tuple in this set. You do this by combining with your original data and keying off each location/date tuple. In SQL, this is a JOIN on the two columns.

Answer 4

This:

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITON BY Location ORDER BY Date DESC) AS rn
        FROM    mytable
        WHERE   rn = 1
        )

or this:

WITH    Locations AS
        (
        SELECT  DISTINCT Location
        FROM    mytable
        )
SELECT  last.*
FROM    Locations
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    mytable
        WHERE   mytable.Location = Locations.Location
        ORDER BY
                mydate DESC
        ) last

The former is more efficient if you don't have an index on (Location, Date), the latter is more efficient if you do.