SQL - How to get distinct record based on a column

How do I write a SQL statement to pick up the distinct record based on ClientID
For example, this is my dataset,
ClientID      code
1227      5200      
1234      5500      
1235      5100      
1235      5600      
1235      5700      
1236      5500      
1237      5200      
1238      5500    

I would like to get the result
1227      5200      
1234      5500      
1235      5100      
1236      5500      
1237      5200      
1238      5500
tommym121Asked:
Who is Participating?
 
Deepak ChauhanSQL Server DBACommented:
Hi ,
   Use this hope so it will display the expected result that u want.

select clientid , code from (
select row_number() over( partition by clientid order by clientid
) as row,clientid , code
from tablename) x where row =1
0
 
AnuroopsunddCommented:
SELECT *
FROM TableName
WHERE clientid IN
(
   SELECT DISTINCT clientid
   FROM TableName
)
ORDER BY clientid
0
 
bosscatCommented:
SELECT DISTINCT(ClientID), code FROM TableName ORDER BY ClientID ASC
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
tommym121Author Commented:
Try both does not get the result I want.
0
 
Wasim Akram ShaikCommented:
try this then..SELECT DISTINCT ClientID, code FROM TableName ORDER BY ClientID ASC
0
 
AnuroopsunddCommented:
SELECT DISTINCT ON (clientid)   clientid, code FROM  tablename ORDER BY  clientid
0
 
bosscatCommented:
Try this instead.

SELECT ClientID, MIN( code )
FROM tablename
GROUP BY ClientID
ORDER BY ClientID ASC
Capture2.PNG
0
 
plummetCommented:
Where there are many rows for that ClientID, do you want to see the lowest Code?
0
 
tommym121Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.