Rizhiy Rizhiy - 7 months ago 8
SQL Question

SQL compare attributes to another row's attribute in the same table

Let's say I have a table of people with their names and Dates of Birth.
How can I select all people's names who have greater DofB than another person while only knowing that persons name and not DofB?

Answer

You have some options, but I like a JOIN here.

Assuming you have a unique name field (using a unique row identifier like id is probably better):

SELECT p.name
  FROM person p
  JOIN person po
    ON po.name = :name_that_you_know
 WHERE p.dob > po.dob

This cross JOINs every person row with the row of the person that you named. The results are filter by the date of birth comparison.

Alternatively you can perform the filter in the JOIN condition:

SELECT p.name
  FROM person p
  JOIN person po
    ON po.name = :name_that_you_know
   AND po.dob < p.dob
Comments