I would like to do a specific query
select distinct name
from tablename
to remove duplicates. But this gives me only the names. From 'select distinct name from table' I would like all columns returned with one where condition:
select *
from tablename
where value= 1
I tried this:
select *
from tablename
where value = 1 and exists (select distinct name
from tablename)
Unfortunately it returns the same data as:
select *
from tablename
where value = 1
Which means that there is a fundamental flaw in my query.
Could someone help me with my query. Thank you in advance.
This query will remove duplicates and retain all the column data:
SELECT DISTINCT myId, name, age
FROM mytable;
If using distinct on mutiple columns is not available on your version of MySQL you can also do this:
SELECT myId, name, age
FROM mytable
GROUP BY myId, name, age;
And on with both of these examples you can use the where clause.
SELECT DISTINCT myId, name, age
FROM mytable
WHERE age > 25;
Same example with group by:
SELECT myId, name, age
FROM mytable
WHERE age > 25
GROUP BY myId, name, age;
To prove these examples work, I have created an SQLfiddle: https://sqlfiddle.com/mysql/online-compiler?id=84247546-cc87-4134-979d-5f56f4727271