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

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?

actually yes; similar to The Impaler's answer.. now to translate that to jpa — iCodeLikeImDrunk, Mar 17 '22 at 20:47

score 2 · Accepted Answer · answered Mar 17 '22 at 20:36

2

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

answered Mar 17 '22 at 20:36

The Impaler

45,731
9
39
76

this looks to be working/doing what i wanted. any performance issues i should consider? also, this is very cool btw. – iCodeLikeImDrunk Mar 17 '22 at 20:46
No major performance issues, unless you are processing millions of rows. – The Impaler Mar 17 '22 at 20:47
i see, thanks! def less than million. also, i am literally limiting the result list to 25, cause requirements – iCodeLikeImDrunk Mar 17 '22 at 20:48

score 2 · Answer 2 · edited Mar 17 '22 at 20:52

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

edited Mar 17 '22 at 20:52

A-Tech

806
6
22

answered Mar 17 '22 at 20:38

joethemow

1,641
4
24
39

score 1 · Answer 3 · answered Mar 17 '22 at 20:38

1

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

answered Mar 17 '22 at 20:38

K1games

131
3

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

3 Answers3