Geek asks and answers

Using HAVING in MySQL

I have a table from which I need to select all persons who have a first name that is not unique, and that this set should only be selected if, among persons with a similar name, everyone has a different surname.

Example:

FirstN    LastN
Bill      Clinton
Bill      Cosby
Bill      Maher
Elvis     Presley
Elvis     Presley
Largo     Winch

I want to receive

FirstN    LastN
Bill      Clinton

or

FirstN    LastN
Bill      Clinton
Bill      Cosby
Bill      Maher

I tried this, but it does not return what I want.

SELECT * FROM Ids
GROUP BY FirstN, LastN
HAVING (COUNT(FirstN)>1 AND COUNT(LastN)=1))

[Edited my post after remark by Alard P. Lovesefer]

+5
source share
4 answers
WITH duplicates AS (
  SELECT firstn --, COUNT(*), COUNT(DISTINCT lastn)
    FROM ids
    GROUP BY firstn
    HAVING COUNT(*) = COUNT(DISTINCT lastn)
       AND COUNT(*) > 1
)
SELECT a.firstn, a.lastn
  FROM ids a INNER JOIN duplicates b ON (a.firstn = b.firstn)
  ORDER BY a.firstn, a.lastn

If mysql does not support WITH, then the internal query:

SELECT a.firstn, a.lastn
  FROM ids a
      ,(SELECT firstn --, COUNT(*), COUNT(DISTINCT lastn)
          FROM ids
          GROUP BY firstn
          HAVING COUNT(*) = COUNT(DISTINCT lastn)
             AND COUNT(*) > 1
        ) b
  WHERE a.firstn = b.firstn
  ORDER BY a.firstn, a.lastn
+7
source

You can try the following:

SELECT A.FirstN, B.LastN
FROM (
SELECT FirstN
FROM Ids
GROUP BY FirstN
HAVING (COUNT(FirstN)>1)
) AS A
INNER JOIN Ids B ON (A.FirstN = B.FirstN)
GROUP BY A.FirstN, B.LastN
HAVING COUNT(B.LastN)=1
+3
source

, ...

SELECT FirstN + LastN as FullName, COUNT(*)
FROM Ids
GROUP BY FirstN + LastN
HAVING COUNT(*) > 1

, .

0

2, , ( , , )

select T.FirstN, T.LastN from  (
      select FirstN, LastN  from Ids
    group by FirstN, LastN
      having count(1) = 1) T
group by FirstN
having count(1) > 1;
0

More articles:


All Articles