December 17, 2007 at 3:07 pm
Let's say we have a table that stores name/value pairs together with a date attribute of the value:
CREATE TABLE test (
name varchar(255) NOT NULL,
date datetime NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY CLUSTERED (name, date)
)
INSERT test (name, date, value) VALUES ('Item1', '1/1/2007', 'abc')
INSERT test (name, date, value) VALUES ('Item1', '1/2/2007', 'def')
INSERT test (name, date, value) VALUES ('Item2', '1/1/2007', 'xyz')
What would be the SQL to retrieve the latest version of the value for all names:
Item1 1/2/2007 def
Item2 1/1/2007 xyz
December 17, 2007 at 3:15 pm
i think this can help
December 17, 2007 at 3:41 pm
Here you go Mark:
SELECT t1.Name, t1.Date, t1.Value
FROM Test t1
INNER JOIN (
SELECT name, MAX(date) as MaxDate
FROM Test
GROUP BY name
) t2
ON t1.Name = t2.Name AND t1.Date = t2.MaxDate
ORDER BY t1.Name
December 17, 2007 at 4:51 pm
Another solution:
select *
from Test e
where e.date = (select max(date) from Test a where e.name = a.name)
order by e.name
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply