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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Deepak ChauhanConnect With a Mentor SQL 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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
All Courses

From novice to tech pro — start learning today.