SQL Query To select distinct records

[ReportDr00]

hello


I have a table with below columns and i noticed in my data the records are being repeated if client has more than one address like below

id name pin dob gender streetadd
1 joe 234 12/1/2000 m 123 test st
1 joe 234 12/1/2000 m 456 lost dr
2 karen 345 1/1/1988 f 34 ditch ct
3 smith 565 3/1/1999 m 987 first st
3 smith 565 3/1/1999 m 564 second st

i have below query
select id,name,pin,dob,gender,streetadd from client group by id,name,pin,dob,gender,streetadd

and i get all above records but i need one record per id
the desired results should look like below

id name pin dob gender streetadd
1 joe 234 12/1/2000 m 456 lost dr
2 karen 345 1/1/1988 f 34 ditch ct
3 smith 565 3/1/1999 m 564 second st

how should i write the sql the give above results?

 

[jbenson001]

do you care which of the addresses show

 

[hmckillop]

If you want to return an address, how do you know which one is the correct one.
What you are asking doesnt really make logical sense unless you dont care about the address field (e.g. anyone could be returned)
If this is the case adjust your query just to return

SELECT
DISTINCT [id], [name], [pin], [dob], [gender], NULL As StreetAdd
from client
--WHERE XXX = 'XX'

This ensures you get all the fields back but wont return two rows for your address.

If you require the street address then you need to define which one is the correct one. based on the info provided I dont think we can identify this.




"I'm living so far beyond my income that we may almost be said to be living apart

 

[jbenson001]

As hmckillop has stated, there really is no way to determine which address you want, if you don't care you can try this. It will show the address from the max(ID) in the group.

Not tested:

Select A.*
from <Your Table> A
inner join
(   Select ID, Max(ID) as MaxID
    from <Your Table>
    group by ID
) B on A.ID = B.ID and A.ID = B.maxDate

 

[ReportDr00]

I would like to get the address in the second row, but i dont have any field to tell that is the recent one.
If i could say always get me the last address it would be nice, like if client has three rows then get me the last row address.

 

[markros]

Only if you keep some field (say, last update) along with the record. Or if you have some flag, say, P for Primary address (assuming you always have only one P record).

Otherwise I don't think you would be able to pick up the last record (though if you use Identity ID for your PK you can get the record with max ID).