There is a table:

+----------------+ |ID |Name | +----------------+ |1000|WorkingJob1| |1001|WorkingJob1| |1002|WorkingJob1| |1003|SomeJob1 | |1004|SomeNewJob1| |1005|SomeNewJob1| |1006|AnotherJob1| |1007|AnotherJob2| +----------------+

You need to write a request that returns the ID and Name so that the Name does not repeat.

Result:

  +----------------+ |ID |Name | +----------------+ |1000|WorkingJob1| |1003|SomeJob1 | |1004|SomeNewJob1| |1006|AnotherJob1| |1007|AnotherJob2| +----------------+

How to insert unique names is understandable using DISTINCT. But how to add another ID is not clear.

  • 2
    select min(ID), Name from table_name group by Name; - MaxU
  • one
    in this case, not using distinct, but using group by name . and for id you need to choose the right aggregate function, judging by the example you want the minimum ID, then you need to apply min (id) - Mike
  • I think the call min to sort extra (order_by) will be just right, but to limit duplicates, of course, group_by is better - Jenyokcoder
  • one
    @Jenyokcoder where is the sorting? if a minimum value is required, then min () is the only option. And most of the DBMS will simply give an error if the aggregate function is not applied to the ID field not participating in the group by. And those DBMS that allow such liberties do not guarantee what kind of ID will be the result and the request can be interpreted as "issuing one random id from the list" - Mike

3 answers 3

 WITH MyTable (id,name,rn) AS ( SELECT id,name,ROW_NUMBER() over (partition by name order by name)rn from #MyTable ) SELECT id,name FROM MyTable WHERE rn=1 order by id

Instead of #MyTable, put the name of your table

     SELECT MIN(ID), MAX(ID), Name FROM dbo.Table1 GROUP BY Name

      Correct answer select min(ID), Name from table_name group by Name;

      Thanks MaxU!