Solved

SQL: how to sequence parent records by child rankings

Posted on 2008-10-07
3
209 Views
Last Modified: 2012-05-05
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
Comment
Question by:Tim85
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 22657108
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
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 125 total points
ID: 22657109
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
 
LVL 1

Author Closing Comment

by:Tim85
ID: 31503728
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question