sql - Remove duplicate rows after joining multiple tables -

i have query eliminate duplicates , shows unique records.

select distinct tblpatient.mrn tblpatient  

output:

mrn       ------ 15257      15283 15285     15290      15291       15302 

however, have additional columns need show want unique mrns.

select        v.patientid,       p.firstname,        p.lastname,        p.dob,        p.mrn,        s.visitid,       v.admiteddate       tblpatient p  join       tblpatientvisit v on p.id = v.patientid  join       tblpatientsmokingscreenorder s on v.id = s.visitid  join       descriptor t on s.smoking_status_dsc_id = descriptor_id         isdate(p.dob) = 1        , convert(date,p.dob) <'12/10/2000'        , v.patienttype = 'i' ,        isdate(v.admiteddate) = 1        , convert(date,v.admiteddate) > '06/16/2013 16:16' 

output:

patientid   firstname   lastname    dob         mrn     visitid admiteddate  --------------------------------------------------------------------------- 1           james       test        6/11/1942   100241  1       54:00.0  10          test3       demographic 4/7/1980    100251  13      39:00.0  5           test2       demographic 8/31/1938   3       12      36:00.0  21          zachary     efron       11/2/1976   100267  24      11:00.0  16          patient     demo        2/28/1943   100260  26      56:00.0  17          alice       wonderland  9/20/1942   100261  20      14:00.0 23          test5       brown       5/6/1965    15285   27      40:00.0 23          test5       brown       5/6/1965    15285   27      40:00.0 

how take script above , refactor show test5 brown once?

if don't have unique value, may need row_number find first record of each unique key:

 select * (   select    v.patientid,   p.firstname,    p.lastname,    p.dob,    p.mrn,    s.visitid,   v.admiteddate,   row_number() on (partition p.mrn order admiteddate desc) row_nm       tblpatient p     join tblpatientvisit v on p.id = v.patientid     join tblpatientsmokingscreenorder s on v.id = s.visitid     join descriptor t on s.smoking_status_dsc_id = descriptor_id        isdate(p.dob) = 1      , convert(date,p.dob) <'12/10/2000'      , v.patienttype = 'i' ,      isdate(v.admiteddate) = 1      , convert(date,v.admiteddate) > '06/16/2013 16:16'  ) res  row_nm = 1 

here need control patient show mrn using different order column(s) in row_number() function.

if have unique value (unique mrn, e.g. admiteddate), can use group find min/max(value) each mrn, , join other values :

 select * table join     (select mrn, max(admiteddate) max_date table ) u  on table.mrn = u.mrn , table.admiteddate = u.max_date