adam adam - 2 years ago 67
SQL Question

Get non duplicates of column x but duplicates of column y

I have a table (call it table myTable).

It has the data like this:

number / name

1 jake
2 chris
3 sally
4 billy
1 tom
5 cathy


(I realize this is a poor setup, but I didn't get the luxury of doing the setup)







I need to query this table so that the results are returned with:

1 jake
2 chris
3 sally
4 billy
5 cathy


OR

2 chris
3 sally
4 billy
1 tom
5 cathy








It does not matter which name gets returned for the duplicated number... only that ONE gets returned.

Here is my not-working attempt:

with
a as (
SELECT number
FROM myTable
),

b as (
SELECT number, name
FROM myTable
)


SELECT a."number", b."name"

FROM a
left join b on a."number" = b."number"

Answer Source

Use GROUP BY to ensure that each number is only returned once, and use an aggregate function to pick one of the values for name:

SELECT number, MAX(name) AS name
FROM yourtable
GROUP BY number
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download