Discuss "Select latest dates" in the SQL Development forum.

Wolffy · #1 (**permalink**) April 11th, 2008, 09:07 AM

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

In this example, I need records 2, 4 and 5. Note, I need the entire record, including the <more columns...> stuff.

Can't seem to sort this one for some reason.

jmurrayhead · #2 (**permalink**) 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....

Wolffy · #3 (**permalink**) 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)

The table was on a remote server, so each attempt was painful.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Discuss "Select latest dates" in the SQL Development forum.

SQL Development - Structured Query Language (SQL) is the talk of databases. Discuss topics such as joins, triggers and other advanced SQL topics.