Brian,

Nice one, I had missed that - must read more carefully next time ;-)

That also means this correlated subquery would be better than my original 
suggestion (though probably not as good as your suggested aggregate 
subquery):
SELECT * FROM Employees E1
WHERE E1.EmpID =
(SELECT MAX(EmpID)
 FROM Employees E2
 WHERE E1.EmpSSN = E2.EmpSSN)

rgds
Phil
>-------------------------------------------
> empID is the primary key.  Each entry for a given employee (discrimated 
by
SSN) has a unique (and always increasing) empID.  Therefore the record with
the latest (maximum) empID also has the latest date.  Since it is a primary
key there are no duplicate EmpID.  I used empID because joining integers
(especially indexed) is preferable to joining/filtering on a date/time
field.

Brian Freeman
(770) 916-0595 ext. 415
Carnegie Technologies/Bluewave Computing 
www.carnegie.com and www.bluewave-computing.com

-----Original Message-----
From: pgtips@m... [mailto:pgtips@m...]
Sent: Monday, January 20, 2003 10:39 AM
To: sql language
Subject: [sql_language] RE: Is it a Union or a Join?


Brian, how does this method ensure that you get the latest record (ie the 
one with the latest RecDate) if there are 2+ records with the same EmpID?  

rgds
Phil
>--------------------------------------------
> depending on your system & optimizer one of these queries may be 
preferable
to running the sub-query for every result:

SELECT e1.* FROM Employees E1
inner join (select max(e2.EmpID),e2.empSSN 
	from employees e2 group by e2.empSSN) as qry_SSN
	on E1.empSSN=qry_SSN.empSSN

or

SELECT * FROM Employees E1
where e1.empID in (select max(e2.EmpID)
	from employees e2 group by e2.empSSN) 

You can try the different variations and see what your results are like.


Brian Freeman
(770) 916-0595 ext. 415
Carnegie Technologies/Bluewave Computing 
www.carnegie.com and www.bluewave-computing.com

-----Original Message-----
From: pgtips@m... [mailto:pgtips@m...]
Sent: Monday, January 20, 2003 6:44 AM
To: sql language
Subject: [sql_language] RE: Is it a Union or a Join?


Peter,

Isn't this just the same as:
SELECT MAX(EmpID), EmpSSN FROM Employees GROUP BY EmpSSN
i.e. your join adds nothing here.

But, as David has pointed out, this will break when adding extra columns.  
I think you do need a correlated subquery, but just one, like this:
SELECT * FROM Employees E1
WHERE E1.RecDate =
(SELECT MAX(RecDate)
 FROM Employees E2
 WHERE E1.EmpSSN = E2.EmpSSN)

This will select the latest record for each EmpSSN.

hth
Phil
>---------------------------------------------------
> David, thanks again - sure appreciate your time.  In the meanwhile, 
using 
y> our suggestion as a springboard I fooled with this a little more and 
came 
u> p with the following:

> SELECT  MAX(InnerE.EmpID), E.EmpSSN
F> ROM  Employees InnerE, Employees E
W> HERE  InnerE.EmpSSN = E.EmpSSN
G> roup By E.EmpSSN

> At least at first blush it *seems* to return what I need.
J> ust hope it doesn't break when I add other columns.

> Again, thank you.
K> indest regards,
P> eter

> > To do this you need to add in another correlated subquery.
S> ELECT DISTINCT
 >    (SELECT EMPid
 >    FROM Employees InnerE
 >    WHERE InnerE.EmpSSN =3D E.EmpSSN)
 >    E.EmpSSN,
 >    (SELECT MAX(RecDate)
 >    FROM Employees InnerE
 >    WHERE InnerE.EmpSSN =3D E.EmpSSN)
F> ROM Employees E

> I don't like using subqueries when something else will do as they are =
p> oor performers, but I don't think that there is any other solution. If =
y> ou didn't need the id, you could use MAX() and GROUP BY, but as it is I 
=
d> on't think you can.

> regards
D> avid Cameron
n> Ow.b2b
d> cameron@i...
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to