[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

SQL: how to sequence parent records by child rankings

I am looking for some help in creating a simple SQL query to retrieve records from two related tables.

The tables (in simplified form) are as follows:

CREATE TABLE parent (
 primary_key integer,
 name varchar(20),
 PRIMARY KEY (primary_key)
);

CREATE TABLE child (
 primary_key integer,
 parent_key integer,
 name varchar(20),
 age integer,
 PRIMARY KEY (primary_key)
);

I need to select records from the parent table that have at least one child, ordered by the age of the oldest child.

It seems a simple enough challenge but I have been unable to crack it. I hope somebody can help.

Many thanks,
Tim
0
Tim85
Asked:
Tim85
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
select p.name, max(c.age) oldest_child_age
  from parent p
  join child c
    on c.parent_key = p.primary_key
 group by p.primary_key, p.name
 order by max(c.age) desc

Open in new window

0
 
momi_sabagCommented:
select t1.name
from parent t1 inner join
 (select count(*) as cnt, max(age) as maxage, parent_key from child group by parent_key) t2
 on t1.primary_key = t2.parent_key)
order by t2.maxage desc
0
 
Tim85Author Commented:
Thanks to both of you for your extremely quick response. I have split the points, since each of you proposed a solution that works. Thanks again.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now