How can I select unique results from a table based on a column? [duplicate]

Question

I have a 'family' table, with the following columns:

• first name
• family name
• age

I want to query this table such that only ONE member of each family will show up on my result list, and that member must be the oldest, and also limit the result to 25.

Example: imagine the following table with ~500k records.

first_name	last_name	age
john	smith	5
mary	smith	10
jack	son	10
joe	daught	10

The expected result list should return [{mary, smith, 10}, {jack, son, 10}, {joe, daught, 10}].

My current solution is basically to pull the whole table, then remove the 'dupes' manually based on age and last name. While this is "ok", once my dataset gets bigger, it's possibly just wasted processing time.

Is this possible using SQL?

Answer 1

You can use ROW_NUMBER() to assign a numeric value by age (oldest to youngest) withing each family. Then you can pick the first one for each family. For example:

select *
from (
  select t.*,
    row_number() over(partition by last_name order by age desc) as rn
  from t
) x
where rn = 1

Answer 2

When using GROUP BY you will need to use an aggregator (MIN(), MAX(), FIRST n, LAST n, etc.) in the SELECT section:

SELECT MAX(u.age), u.last_name 
  FROM users AS u
  GROUP BY u.last_name

Answer 3

select first_name from table_name group by last_name having max(age)