How to write a query to get 1 row each based on 3 columns?

Question

I have a table Employee

create table emp(
  id int,
  name varchar(50),
  age int,
  salary int
);

Total rows:

id  name    age salary
1   Tom     22  2000
2   Mike    12  2000
3   Niki    42  2000
4   Tom     22  2000
5   Yuki    52  2000
6   Niki    42  2000

Here in this table, there are 2 employees (Tom and Niki) with same name, age and salary.

Is there any query which i can write to get below output (In case of matching of name, age and salary, i should get row with minimum id).

id  name    age salary
1   Tom     22  2000
2   Mike    12  2000
3   Niki    42  2000
5   Yuki    52  2000

How can i achieve this?

@JuanCarlosOropeza, good point. I didn't think about them needing the `id` at all, since that wouldn't work with the `Distinct` anyway. — computercarguy, Jul 23 '19 at 16:43

score 1 · Accepted Answer · answered Jul 23 '19 at 16:16

1

Use group by name, age, salary with min(id) aggregation :

select min(id) as id, name, age, salary
  from emp
 group by name, age, salary

answered Jul 23 '19 at 16:16

Barbaros Özhan

1 Answers1