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?
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