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.
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
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.
