SQL – select distinct only on one column [duplicate]

by

A very typical approach to this type of problem is to use row_number():

select t.*
from (select t.*,
             row_number() over (partition by number order by id) as seqnum
      from t
     ) t
where seqnum = 1;

This is more generalizable than using a comparison to the minimum id. For instance, you can get a random row by using order by newid(). You can select 2 rows by using where seqnum <= 2.

Related Contents:

  1. How to select only the first rows for each unique value of a column?
  2. How to create a unique index on a NULL column?
  3. Eliminating duplicate values based on only one column of the table
  4. Produce DISTINCT values in STRING_AGG
  5. Get top 1 row of each group
  6. Solutions for INSERT OR UPDATE on SQL Server
  7. How to select all records from one table that do not exist in another table?
  8. How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
  9. Select statement to find duplicates on certain fields
  10. Difference between numeric, float and decimal in SQL Server
  11. How to set variable from a SQL query?
  12. Convert Datetime column from UTC to local time in select statement
  13. Inserting data into a temporary table
  14. Change Schema Name Of Table In SQL
  15. How to calculate age (in years) based on Date of Birth and getDate()
  16. How do I use ROW_NUMBER()?
  17. Calculate a Running Total in SQL Server
  18. Real life example, when to use OUTER / CROSS APPLY in SQL
  19. Getting result of dynamic SQL into a variable for sql-server
  20. How to assign an exec result to a sql variable?
  21. How do I delete from multiple tables using INNER JOIN in SQL server
  22. How to print GETDATE() in SQL Server with milliseconds in time?
  23. How to force a SQL Server 2008 database to go Offline
  24. How to find third or nᵗʰ maximum salary from salary table?
  25. Rename a constraint in SQL Server?
  26. Group by minimum value in one field while selecting distinct rows
  27. Why does Sql Server keep executing after raiserror when xact_abort is on?
  28. Conditional WHERE clause in SQL Server
  29. SQL Server Invalid Column name after adding new column
  30. Create parameterized VIEW in SQL Server 2008
  31. SQL Join Table Naming Convention [closed]
  32. Efficient latest record query with Postgresql
  33. Limit on the WHERE col IN (…) condition
  34. Inner Joining three tables
  35. SQL Server find and replace specific word in all rows of specific column
  36. How to select data of a table from another database in SQL Server?
  37. How to get DATE from DATETIME Column in SQL? [duplicate]
  38. How to list files inside a folder with SQL Server
  39. How do I determine if a database role exists in SQL Server?
  40. Postgres Case Sensitivity
  41. Comparing two bitmasks in SQL to see if any of the bits match
  42. T-SQL: Selecting Column Based on MAX(Other Column)
  43. How do you strip a character out of a column in SQL Server?
  44. SQL Server Convert Varchar to Datetime
  45. Is there a opposite function to ISNULL in sql server? To do Is not null?
  46. CTE within a CTE
  47. How to get last 7 days data from current datetime to last 7 days in sql server
  48. Can I set ignore_dup_key on for a primary key?
  49. SQL Last 6 Months
  50. how to declare global variable in SQL Server..?

Leave a Comment