How to select only the first rows for each unique value of a column?

by

A very simple answer if you say you don’t care which address is used.

SELECT
    CName, MIN(AddressLine)
FROM
    MyTable
GROUP BY
    CName

If you want the first according to, say, an “inserted” column then it’s a different query

SELECT
    M.CName, M.AddressLine,
FROM
    (
    SELECT
        CName, MIN(Inserted) AS First
    FROM
        MyTable
    GROUP BY
        CName
    ) foo
    JOIN
    MyTable M ON foo.CName = M.CName AND foo.First = M.Inserted

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. What is the difference between using a cross join and putting a comma between the two tables?
  3. how does SELECT TOP works when no order by is specified?
  4. SQL where clause odd behavior [closed]
  5. How do I escape a single quote in SQL Server?
  6. Simple way to transpose columns and rows in SQL?
  7. Only inserting a row if it’s not already there
  8. Unpivot with column name
  9. Selecting data from two different servers in SQL Server
  10. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  11. SQL JOIN vs IN performance?
  12. Most efficient way in SQL Server to get date from date+time?
  13. Convert varchar into datetime in SQL Server
  14. TSQL Pivot without aggregate function
  15. How to generate a range of dates in SQL Server
  16. SQL RANK() versus ROW_NUMBER()
  17. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  18. How do I delete from multiple tables using INNER JOIN in SQL server
  19. SQL : BETWEEN vs =
  20. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  21. How do I perform a GROUP BY on an aliased column in SQL Server?
  22. How do I make a composite key with SQL Server Management Studio?
  23. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  24. Best equivalent for IsInteger in SQL Server
  25. Store multiple bit values in a single table column
  26. Syntax of for-loop in SQL Server
  27. SQL Server equivalent to MySQL enum data type?
  28. SQL – Call Stored Procedure for each record
  29. COALESCE Function in TSQL
  30. Convert bigint to datetime

Leave a Comment