| |
| |||||||
| Sponsored Links |
 |
| | LinkBack | Thread Tools | Display Modes |
|
#1
April 11th, 2008, 09:07 AM
| ||||
| ||||
 Select latest dates Every once in a while, I have a query that I just can't wrap my head around. I figure by posting here 1 of 2 things will happen. Somebody will come up with the query or I'll figure it out 30 seconds after I hit save. I need to SELECT the latest record of type INIT for each user. Each user may have multiple INIT records, but only the latest one has any meaning. The table has a compound key consisting of Bldg, UserNo (yup, each building maintains its own records of employees) and a full time stamp. Thus: Code: Bldg ID TS Type OtherStuff A 1 03-01-2001-07:30:25 INIT <more columns...> --1 A 2 02-01-2000-12:30:22 INIT <more columns...> --2 A 1 03-02-2001-11:12:52 ADJ <more columns...> --3 B 1 02-03-2002-15:00:12 INIT <more columns...> --4 A 1 04-17-2005-00:12:12 INIT <more columns...> --5 Can't seem to sort this one for some reason. |
| Sponsored Links |
|
#2
April 11th, 2008, 09:22 AM
| ||||
| ||||
| By the looks of it, the same user could be in multiple buildings, correct? In your post, User ID 1 is in building A and B. Hmmm...that makes it a little more tricky. At first I was thinking a SELECT DISTINCT would help, but then you wouldn't get the other user record if that user belonged to more than one building....
__________________ jmurrayhead If you agree with me... click the  icon! If my post solved your problem, click the  button in the lower right-hand corner of the post.If you like it here...throw us a few bones to help support us. Join our Folding team: DeveloperBarn Folding |
|
#3
April 11th, 2008, 09:25 AM
| ||||
| ||||
| Yup, about 2 minutes after I posted, I had a flash of insight. Code: SELECT a.* FROM aTable a JOIN (select bldg, id, max(ts) as lastDate from aTable where type = 'INIT' group by bldg,id) k ON (a.bldg= k.bldg and a.id = k.id and ts = lastDate) |
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
