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

Question

Let s say I have a table of customer addresses   ----------------------- ------------------------            CName                AddressLine          ----------------------- ------------------------     John Smith              123 Nowheresville          Jane Doe                456 Evergreen Terrace      John Smith              999 Somewhereelse          Joe Bloggs              1 Second Ave             ----------------------- ------------------------   In the table  one customer like John Smith can have multiple addresses  I need the SELECT query for this table to return only first row found where there are duplicates in  CName   For this table it should return all rows except the 3rd  or 1st - any of those two addresses are okay but only one can be returned   Is there a keyword I can add to the SELECT query to filter based on whether the server has already seen the column value before

User · Answer

This will give you one row of each duplicate row. It will also give you the bit-type columns, and it works at least in MS Sql Server.

(select cname, address 
from (
  select cname,address, rn=row_number() over (partition by cname order by cname) 
  from customeraddresses  
) x 
where rn = 1) order by cname

If you want to find all the duplicates instead, just change the rn= 1 to rn > 1. Hope this helps

User · Answer

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

User · Answer

You can use row number   to get the row number of the row  It uses the over command - the partition by clause specifies when to restart the numbering and the order by selects what to order the row number on  Even if you added an order by to the end of your query  it would preserve the ordering in the over command when numbering   select   from mytable where row number   over partition by Name order by AddressLine    1

User · Answer

In SQL 2k5   you can do something like    with cte as     select CName  AddressLine    rank   over  partition by CName order by AddressLine  as  r    from MyTable   select CName  AddressLine from cte where  r    1

User · Answer

to get every unique value from your customer table  use SELECT DISTINCT CName FROM customertable   more in-depth of w3schools  https   www w3schools com sql sql distinct asp

User · Answer

You can use the row numer   over partition by      syntax like so   select   from   select     ROW NUMBER   OVER PARTITION BY CName ORDER BY AddressLine  AS row from myTable   as a where row   1   What this does is that it creates a column called row  which is a counter that increments every time it sees the same CName  and indexes those occurrences by AddressLine  By imposing where row   1  one can select the CName whose AddressLine comes first alphabetically  If the order by was desc  then it would pick the CName whose AddressLine comes last alphabetically

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to select

Examples related to unique