May 14, 2008 at 10:27 am
Hi everyone,
I have a log table which contains a list of items found on shelves during many stock-take operations. Sample data could look like this:
Item_ID Location username ScanDate
1 A Jim 21-02-2008
2 A Jim 21-02-2008
3 B Jim 21-02-2008
4 B Sue 26-02-2008
5 B Sue 26-02-2008
3 C Tom 21-02-2008
1 C Tom 21-02-2008
2 C Dave 25-03-2008
4 A Tom 29-02-2008
From this data I want to get a distinct list of the locations, along with the latest date that each location was scanned and who did the scanning. So the desired result for the above would be
Location DateLastScanned Username
A 29-02-2008 Tom
B 26-02-2008 Sue
C 25-03-2008 Dave
The problem is that a simple DISTINCT command brings back distinct combinations of all 3 fields which is no good to me. I just want the latest date that each location was scanned, and who did the scanning. Any ideas?
May 14, 2008 at 11:06 am
Use a subselect
select distinct (location)
from (select a, b, c from table) a
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 14, 2008 at 11:31 am
; with a as (select *, row_number() over (partition by Location order by ScanDate desc) as Row from YourTable)
select * from a where Row = 1
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 15, 2008 at 4:29 am
That's done the trick - thanks to both of you for your quick responses 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply