CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
WeSt
WeSt

Reputation: 929

return none duplicated mysql row

Let's say I have a table with two columns begin_timestamp and images.

First I do this statement

SELECT images 
FROM myTable 
WHERE begin_timestamp < 1546646400 && images != 'default.png';

This will return me a list of strings like :

'123.jpg'
'abc.jpg'
'test.jpg'

What I want as second is to check if one or more items of that list has a duplication in the hole table. If yes, kick that string out of the list. Like if a other row in the table contains 123.jpg as image value I just want the list like 

[abc.jpg, test.jpg]

My question is now, how can I combine the first and the second step to one mySQL statement?

Upvotes: 0

Views: 62

Answers (3)

Felk
Felk

Reputation: 8224

You can achieve this using GROUP BY.

First, let's do the selection as above:

SELECT images
FROM myTable
WHERE begin_timestamp < 1546646400 AND images != 'default.png'
GROUP BY images;

now, because we have grouped by images, we have access to aggregate functions. We can add the desired filter like this at the end of the query:

HAVING COUNT(images) = 1

Note that because we have grouped by images, that now is the only thing we can directly query in the SELECT part. If you want to select any other fields, you need to do so through an aggregate function as well, e.g. SELECT images, MIN(some_field). Or if images is the primary key, use it in a subquery or join.

If you need to remove duplicates before applying the WHERE filter condition, you can use a subquery like this:

SELECT images FROM (
    SELECT images, MIN(begin_timestamp) as begin_timestamp
    FROM myTable
    GROUP BY images
    HAVING COUNT(images) = 1)
WHERE begin_timestamp < 1546646400 AND images != 'default.png';

Upvotes: 4

Deependra Bhandari
Deependra Bhandari

Reputation: 51

declare @image varchar(max) set @image=''

SELECT @image=@image+','+ images FROM myTable WHERE begin_timestamp < 1546646400 AND images != 'default.png' GROUP BY images HAVING COUNT(*) = 1

select @image

Upvotes: 0

Omar Matijas
Omar Matijas

Reputation: 71

Other way to do it is with NOT EXISTS.

I assumed you have and id field in the table:

SELECT images 
FROM myTable m1
where m1.begin_timestamp < 1546646400 && m1.images != 'default.png'
and NOT EXISTS(select 1 from myTable m2 where m1.images = m2.images and m1.id != m2.id)

Upvotes: 2

Related Questions