Tsql Get the latest records from a table
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 dates) rows for all locations, so leave the example for row 2 and row 7. How can I do this?
I can select the last one for 1 location with the first date for one location, I tried to group by location, date, but then I missed the pace ....
Regards, Michelle
source to share
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 at is when maxdat for a given location can run more than once, a join will return more than one result for a location, date combination.
You may want to choose which of these results or whatever you want to return.
source to share
It:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Location ORDER BY Date DESC) AS rn
FROM mytable
) a
WHERE a.rn = 1
or that:
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.
source to share
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
NB This assumes that each date is recorded only once for each location.
To understand how to do this, remember that you are dealing with sets, so start with a simple bit:
SELECT location, MAX(date)
FROM yourTable
GROUP BY location
This gives you the most recent date for each location. Then, you want to extract the temperature for each tuple in that set. You do this by combining with your original data and sampling each location / tuple of date. In SQL, this is a JOIN for two columns.
source to share