CODEWITHSUNDEEP
sqlsql-servert-sqlselectdistinct
pmh
pmh

Reputation: 69

Selecting distinct values from table using two columns

I have following data in the table.

Id   Name
1    Abc
2    Abc
3    Xyz
4    Xyz
5    def
6    def

I want following results from the query

Id  Name
1   Abc
2   Xyz
3   def

I want to avoid duplicates in the name column.

Any help is greatly appreciated.

Select distinct 
    id, name 
from table A

will not work as ids are having a different values.

Upvotes: 1

Views: 98

Answers (2)

crthompson
crthompson

Reputation: 15865

Use a group by instead.

select
  min(id), [name]
from
  tableA
group by [name]

Note that in your example, the ids that corresponds with Xyz are 3 and 4, so getting a 2 next to Xyz is only possible if you break the integrity of the table. If you are just looking for an auto number next to the ids you can do this:

SELECT row_number() OVER (ORDER BY min(id)) id,
       name 
  FROM tableA
group by name

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can get your specific result using:

select row_number() over (order by min(id)) as id, name
from table A
group by name;

Renumbering the rows seems strange, but row_number() will do that.

Upvotes: 0

Related Questions