There is a certain table tbl :

 id | f_key | alive ------------------- | |

There are fields, but they do not matter.

I want to find out if I have more than one live record at the moment (alive = true) related to one 'f_key'.

What can I do myself:
I can write a simple request

 select * from tbl where alive = true order by f_key;

and see for yourself: if there are identical f_key in two or more lines in a row, then this is what I'm looking for.

But I would like to automate this work somehow. I understand that the question is very simple, and you just need to vkoryachit somewhere in the condition aggregate function count() , the value of which is more than one, but I'm confused where.

  • You would specify the DBMS and the version, for many there is a more elegant solution ... - Akina

2 answers 2

If I understand you correctly, then such a request will return the amount:

 select count(*) from tbl where alive = true group by f_key;

If you want to know which keys are duplicated, you can add f_key to the output:

 select count(*),f_key from tbl where alive = true group by f_key;

If you need exactly the entire record, then this is the request

 select * from tbl t1 where ( Select count(f_key) from tbl t2 where (alive = true) and // Эта строка если дубли нужны только среди "живых" t2.f_key=t1.f_key )>1 and (alive = true)
  • I need to get not the number of such records, but the records themselves - Oleg
  • Then a minute =) - Vladimir Klykov
  • Thank. It seems this is what I need, I'll check it out now. It seems the second alive = true no longer needed - Oleg
  • @Oleg without the second will display all entries where more than 2 live f_key, i.e. select all records there are at least 2 live f_key and all dead for the same f_key - Vladimir Klykov
  • Many times I had to re-read the last sentence, but in the end I finally got it =) - Oleg

Get all entries:

 with cte as ( select id, f_key, alive, sum(alive='alive') over (partition by f_key) cnt from tbl ) select id, f_key, alive from cte where cnt > 1;

Get only live recordings:

 with cte as ( select id, f_key, alive, count(*) over (partition by f_key) cnt from tbl where alive='alive' ) select id, f_key, alive from cte where cnt > 1;

Dialect: MySQL 8+.