MySQL queries for all records that a field does not duplicate

  • 2020-05-07 20:34:44
  • OfStack

Suppose you now have the following N record called book
id author title
1 aaa AAA
2 bbb BBB
3 ccc CCC
4 ddd DDD
5 eee AAA
Now you want to query all of the five records that title does not repeat
select distinct title,author from book this is not possible because distinct can only work on one field
Want to ask how should write
The answer:

select a.* from book a right join ( 
select max(id) id from book group by title) b on b.id = a.id 
where a.id is not null 

If you choose the record that matches the first, then min(id) will do


select a.* from book a right join ( 
select min(id) id from book group by title) b on b.id = a.id 
where a.id is not null 

Related articles: