CODEWITHSUNDEEP
sqlsql-servert-sqldistinctdistinct-values
Mo T
Mo T

Reputation: 450

Select distinct columns sql server

I have a table which contain identity primary key id, CNum, CUID and some other columns. I want to select distinct records based on CNum and CUID, in other words if two records have the same CNum and CUID I want to get the top one, I tried to group by but it will not work since the want the whole row.

with a as (SELECT distinct CNum, CUID
FROM Con) 
select c.CNum from Con c inner join
a on a.CNum = c.CNum
and a.CUID= c.CUID
order by id

This approach still gets duplicate records.

Anyone knows how to solve the problem?

Upvotes: 0

Views: 287

Answers (3)

jeroenh
jeroenh

Reputation: 26782

If you want to select the first row of a set of rows with the same characteristics, you can use the ROW_NUMBER() function and PARTITION BY clause. Something like this:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CNum, CUID ORDER BY Id) AS ROWNUM 
    FROM Con
) x WHERE x.ROWNUM = 1

The subquery adds a row number to each row having the same values for CNum/CUID.

Upvotes: 3

Patrick Hofman
Patrick Hofman

Reputation: 156928

Maybe I am mistaking, but I don't think you should include from Con c in your query.

You already included Con in the CTE, getting the distinct values there. If you use it here again, there is no point for your CTE.

Upvotes: 0

huMpty duMpty
huMpty duMpty

Reputation: 14460

You missing distinct from final select statement 

select distinct  c.CNum 
from Con c 
     inner join a on a.CNum = c.CNum and a.CUID= c.CUID
order by id

Upvotes: 0

Related Questions