SQL Select with multiple IDs

Question

I have an Address History table with three fields: EmpID, Address, AddrID.

Every time I add a new address, I also increment the Address ID (AddrID) by 1 for that particular employee.

EmpID | AddrID | Address
-------------------------------
 1    |     1  | 1234 First Ave
 1    |     2  | 2145 First Ave
 1    |     3  | 1111 First Ave

 2    |     1  | 1001 Second St
 2    |     2  | 1002 Second St
 2    |     3  | 1003 Second St
 2    |     4  | 2222 Second St

 3    |     1  | 3332 Third Lane
 3    |     2  | 3333 Third Lane

 4    |     1  | 4444 Fourth Way

How do I get the most recent address (highest Address ID) for each employee? Ideally, I should be able to return:

EmpID | AddrID | Address
-------------------------------
 1    |     3  | 1111 First Ave
 2    |     4  | 2222 Second St
 3    |     2  | 3333 Third Lane
 4    |     1  | 4444 Fourth Way

So far I have either returned too many results (ie, every Employee, every AddrID 1, and every Address associated with the two), or too few results (ie, every Employee with an AddrID 4 - just Employee 2).

I have tried using Distinct, Group By, Order By, Having, and Self-Joins to no avail.

What am I missing?

I did not realize I could only accept one answer. They are both correct. NJK's answer is succinct. BlueFeet's answer is explicit. — Phillip Deneka
– Phillip Deneka, Commented Feb 13, 2013 at 19:11
Also, voting down a perfectly good question is simply spiteful. I asked the question clearly and concisely with great examples and walkthrough for others to follow. Please remove your -1. — Phillip Deneka
– Phillip Deneka, Commented Feb 13, 2013 at 19:18
How do you know who down-voted? Please don't accuse people of crap unless you have evidence. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 13, 2013 at 19:51
While I do not have substantive proof, I did watch it happen within moments of his comment as I was browsing my history for responses to another pending question. I find that highly circumstantial. Point taken, Aaron. — Phillip Deneka
– Phillip Deneka, Commented Feb 13, 2013 at 19:54

Taryn · Accepted Answer · 2012-10-04 21:17:36Z

5

You can use a subquery that gets the MAX() addrid for each empid:

select t1.empid,
  t1.addrid,
  t1.address
from table1 t1
inner join
(
  select max(addrid) addrid, empid
  from table1
  group by empid
) t2
  on t1.empid = t2.empid
  and t1.addrid = t2.addrid

See SQL Fiddle With Demo

The inner query will return the max addrid and the empid then you join your table to that result on those two values this will limit the records that get returned.

edited Oct 4, 2012 at 21:17

answered Oct 4, 2012 at 21:11

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kermit · Accepted Answer · 2013-02-13 19:55:02Z

4

The following should work:

SELECT * 
FROM   (SELECT empid, 
               Max(addrid) AS AddrID 
        FROM   t1 
        GROUP  BY empid) a 
       JOIN t1 b 
         ON b.empid = a.empid 
            AND b.addrid = a.addrid

See it in action

edited Feb 13, 2013 at 19:55

answered Oct 4, 2012 at 21:14

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

2 Comments

Jake Feasel Over a year ago

njk - quick SQL Fiddle tip: you can copy and paste OP's sample data into the "Text to DDL" tool to quickly generate the scripts needed to work with it. For example, in this case it would produce this: sqlfiddle.com/#!2/7d725/1 As you can imagine, doing this makes answering questions with SQL Fiddle much less tedious. Cheers!

Kermit Over a year ago

@JakeFeasel Cheers for that tip!

Brian Hoover · Accepted Answer · 2012-10-04 21:11:50Z

0

I would probably organize the table differently, but you could do something with a sub-join

    select * from address_history 
    join 
    (select EmpID, max(AddrID) max_address from Address_History group by empId) as latest_addresses 
       on address_history.empid = latest_addresses 
       and address_history.addrId = latest_addresses.max_address

answered Oct 4, 2012 at 21:11

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

Collectives™ on Stack Overflow

SQL Select with multiple IDs

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related