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?
>Solution :
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