skip to Main Content

I have a table (tng_people) and I want Sql to select all the people with the same birthday.
I want personId, Lastname, Surname, Birthdate and deathdate.

SELECT P.birthDate, DATE_FORMAT(birthDate,'%d %b %Y'), GROUP_CONCAT(CONCAT(familyName, " ",givenName)) AS PersonNames
  FROM person P
 WHERE concat( givenName, familyName, birthDate) IS NOT NULL
 GROUP BY P.birthDate;

2

Answers


  1. You could use an exists clause to filter for persons for whom another person exists with the same birthday:

    SELECT  DATE_FORMAT(birthDate, '%d %b %Y')
    ,       CONCAT(familyName, ' ', givenName)
    FROM    person P
    WHERE   EXISTS
            (
            SELECT  *
            FROM    P2
            WHERE   P.birthDate = P2.birthDate
                    AND P.id <> P2.id
            )
    
    Login or Signup to reply.
  2. Instead of GROUP BY you could use ORDER BY to bring persons with the same birthday together :

    SELECT DATE_FORMAT(birthDate,'%d %b %Y'),
           GROUP_CONCAT(CONCAT(familyName, " ", givenName)) AS PersonNames
    FROM person
    WHERE concat( givenName, familyName, birthDate) IS NOT NULL
    ORDER BY birthDate;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search