All geek questions in one place

Sqlite gets records with the same column value

I have an SQLite DB in which there are people LastName, FirstName, Department, and I need to make a query that shows me all the people with the same First and Last Names. I found the following expression that supposedly does what I want for a single field, however, it doesn't seem to work for me when I try to use it to pull out all records with the same last name. How can i do this?

Select myField From myTable Group by myField Where count(myField)>1
+4
source share
2 answers

to try:

 Select firstname,LastName,Count(*) From myTable Group by firstname,LastName HAVING Count(*)>1

GROUP BY concatenates strings where named values ​​are the same.
HAVING deletes groups that do not meet this condition.

The above query will list first and last names, as well as the number of duplicates for all first / last names that actually have duplicates.

+7
source

First, you need to use HAVING, not WHERE, to get the result of GROUPed BY:

  SELECT myField FROM myTable GROUP BY myField HAVING COUNT(myField) > 1

Secondly, you can expand it to several columns, for example:

  SELECT MyCol1, MyCol2 FROM MyTable GROUP BY MyCol1, MyCol2 HAVING COUNT(*) > 1
+2
source

Source: https://habr.com/ru/post/1304290/

More articles:


All Articles