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

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.

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

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

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

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.

Collectives™ on Stack Overflow

Select distinct columns sql server

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related