Reputation: 1833
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?
Upvotes: 174
Views: 461494
Reputation: 5109
You can use the row_number() over(partition by ...)
syntax like so:
select * from
(
select *
, ROW_NUMBER() OVER(PARTITION BY CName ORDER BY AddressLine) AS rownum
from myTable
) as a
where rownum = 1
What this does is that it creates a column called rownum
, which is a counter that increments every time it sees the same CName
, and indexes those occurrences by AddressLine
. By imposing where rownum = 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.
Upvotes: 65
Reputation: 303
select amount
from (
select distinct(amount)
from orders
order by amount desc
limit 3
)
order by amount asc
limit 1;
Upvotes: -2
Reputation: 305
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
Upvotes: -5
Reputation: 601
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
Upvotes: 4
Reputation: 32737
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
Upvotes: 36
Reputation: 693
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
Upvotes: 8
Reputation: 432742
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
Upvotes: 191