reg. removing duplicates with latest date and unique member ID

[bimal13]

I currently have a table with these fields:

analname,member_id,member_name,dob,sex,dos,spec_num,smc,testname,dxcode1,loinc,ndec_low,ndec_hi,rslt_dec,abn_code,
rsltabrv,cpt_code,pay_acct,acctname,elig_sw,ord_acct,docname

I would like to have a query that will show all these fields without
any duplicates in the member_id field, but leaving the record with the
latest date in the event of a duplicate.

If someone can help, it would be highly appreciated.

Thanks

Bimal

 

[lespaul]

something like:

(typed not tested)

SELECT analname,B.member_id,member_name,dob,sex,B.dos,spec_num,smc,testname,dxcode1,loinc,ndec_low,ndec_hi,rslt_dec,abn_code,
rsltabrv,cpt_code,pay_acct,acctname,elig_sw,ord_acct,docname
FROM [b]TableName[/b] A INNER JOIN 
(SELECT MemberID, Max(DOS) FROM [b]TableName[/b] GROUP BY MemberID) B ON A.MemberID = B.MemberID And A.DOS = B.DOS

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com