select non-dup records

[xg]

My table looks like this:

recno sem sid last first address
1 3 88-123-4567 Li Jun 345 Fullerton Road
2 3 88-123-4567 Li Jun 345 Fullerton Road
3 2 88-123-4567 Li Jun 432 Gale Ave
4 2 88-765-4321 Wu Shu 123 Colima Road
5 1 88-765-4321 Wu Shu 123 Colima Road
....
....

people listed multiple times. I want to select one record for each person
and I want the highest 'sem'.

So in this case, I want to select recno 1 and 4.

How to construct a query? Sort decending by sem and group all other fields?

Thanks.

 

[Michel Walsh]

Hi,


Many possible solutions. One of them is:


SELECT a.sid, a.sem, LAST(a.recno), LAST(a.last), LAST(a.first),
LAST(a.address)
FROM mytable as a INNER JOIN mytable as b
ON a.sid=b.sid
GROUP BY a.sid, a.sem
HAVING a.sem=MAX(b.sem)


Other solutions at http://www.mvps.org/access/queries/qry0020.htm


Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Solution 1: Use a coordinated subquery in the where clause

SELECT RecNo, Sem, Sid, [Last], [First], Address
FROM YourTable as YT
WHERE Sem =
(SELECT Max(Temp.Sem)
FROM YourTable as Temp
WHERE. Temp.sid = Yt.Sid)

Solution 2: Use a subquery in the FROM clause (if this works it will be
faster than the above)

SELECT YT.RecNo, Yt.Sem, Yt.Sid, Yt.[Last], Yt.[First], YT.Address
FROM YourTable as YT INNER JOIN
(SELECT Temp.SID, Max(Temp.Sem) as LastSem
FROM YourTable as Temp
GROUP BY Temp.SID) as YT2
ON YT.SID = YT2.Sid AND YT.Sem = YT2.LastSem