Select distinct columns sql server

Question

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?

What do you mean with "I tried group by group by but it will not work since the want the whole row"? Are you showing the whole query you tried? — jeroenh
– jeroenh, Commented Apr 28, 2014 at 8:52

jeroenh · Accepted Answer · 2014-04-28 09:43:06Z

3

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.

edited Apr 28, 2014 at 9:43

answered Apr 28, 2014 at 8:57

jeroenh

26.9k11 gold badges77 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

huMpty duMpty · Accepted Answer · 2014-04-28 08:45:42Z

0

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

answered Apr 28, 2014 at 8:45

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

Comments

Patrick Hofman · Accepted Answer · 2014-04-28 08:48:22Z

0

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.

answered Apr 28, 2014 at 8:48

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Collectives™ on Stack Overflow

Select distinct columns sql server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related