'Mysql query to get distinct row with column having null and duplicate values
So I have a weird situation in which I have a table like this :
mysql> select * from test;
+-------+------+
| name | sal |
+-------+------+
| agent | 1000 |
| agent | 2000 |
| NULL | 3000 |
| NULL | 4000 |
| smith | 5000 |
| smith | 6000 |
| neo | 7000 |
+-------+------+
I want to return a data set which looks like this :
+-------+------+
| name | sal |
+-------+------+
| agent | 1000 |
| NULL | 3000 |
| NULL | 4000 |
| smith | 5000 |
| neo | 7000 |
+-------+------+
Meaning I want to fetch unique rows wherever name is duplicated, but fetch all rows as it is when name is null or is not duplicated.
I have written this query below in order to achieve that and it works fine. But I want to optimize it.
select *
from test
where sal in (
select sal from (
select min(sal) as sal
from test
group by name
union
select sal
from test where name is null
) t
order by sal);
Queries for creating this sample data -
create table test (name text, sal int);
insert into test values ('agent',1000);
insert into test values ('agent',2000);
insert into test values (null,3000);
insert into test values (null,4000);
insert into test values ('smith',5000);
insert into test values ('smith',6000);
insert into test values ('neo',7000);
Can anyone help me with that? I know that we shouldn't use IN to fetch data because that will increase the query time a lot in production.
Any help is appreciated!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
