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

i have table 3 columns: name, surname, email. data in columns not unique.

i need result matches following criteria:

• select 3 columns
• email records should unique
• there should 1 record per email

that means select distinct isn't applicable because retrieve multiple email records.

any ideas?

you didn't specify dbms, systems support "windowed aggregate functions":

with cte  ( select email, name, surname,       row_number() on (partition email order name) rn    tab  ) select email, name, surname tab rn = 1 

this assigns ranking each email , returns first.