How do I form this SQL-
-
Lets says I have a table with the following values (date and time in separate fields)
somereference date time status owner
1234 Jan 1 12:00 pass joe
1234 jan 1 15:00 fail joe
4321 feb 15 2:30 pass mark
9999 mar 18 3:30 fail sally
9999 mar19 1:00 pass sally
the results I need is the "last" chronological status for each reference, so the results would be
1234 jan 1 15:00 fail joe
4321 feb 15 2:30 pass mark
9999 mar19 1:00 pass sally
-
Are these the only columns you have - no primary key or ID column ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Here's a link to something similar - except in this case it was the first occurence not the max...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=191860#bm192527
**ASCII stupid question, get a stupid ANSI !!!** -
SELECT a.somereference, a.[date], a.[time], a.status, a.owner
FROM #Table a
INNER JOIN (
SELECT x.somereference, MAX(CAST('1904 '+x.[date]+' '+x.[time] as datetime)) AS [MaxDate]
FROM #Table x
GROUP BY somereference
) b
ON b.somereference = a.somereference
AND b.MaxDate = CAST('1904 '+[date]+' '+[time] as datetime)
Far away is close at hand in the images of elsewhere.
Anon. -
That works perfect! Thanks. Someday I'll shed my Oracle knowledge and learn Transact SQL.
-
Way to go, Ron
-
Way to go, Ron
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply