CODEWITHSUNDEEP
sqlsql-server
Menas Eru
Menas Eru

Reputation: 118

SQL Server : select only one row based on a field when there are several rows

I have a table with 3 columns: Name, Surname, Email. Data in those columns is not unique.

I need to get result that matches following criteria:

That means SELECT DISTINCT isn't applicable because it could retrieve multiple email records.

Any ideas?

Upvotes: 0

Views: 188

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

If you want to show all unique names associated with each email with one row per email you can use string aggregation.

If using MySQL (you didn't specify the database):

select group_concat(distinct name order by name separator ', ') as names,
       group_concat(distinct surename order by name separator ', ') as surenames,
       email
  from tbl
 group by email

If using PostgreSQL, string_agg is the equivalent. If using Oracle, listagg.

If you just arbitrarily want any name associated with the email, and you don't care which name, just as long as it's only one, you can use the previous answers.

However if your database doesn't support the with clause or window functions (ie. MySQL), you can use the below to arbitrarily show only one name and surname per email:

select x.*, y.surname
  from (select email, max(name) as name from tbl group by email) x
  join tbl y
    on x.name = y.name
   and x.email = y.email

This will show the correct surname for the given name because it picks the max(name) first and then gets the surname for that name and email.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

You didn't specify your DBMS, but most systems support "Windowed Aggregate Functions":

with cte as
 ( select Email, Name, Surname,
      row_number() over (partition by Email order by Name) as rn
   from tab
 )
select Email, Name, Surname
from tab
where rn = 1

This assigns a ranking to each email and returns only the first.

Upvotes: 2

Related Questions