Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL: how to sequence parent records by child rankings

Posted on 2008-10-07
3
Medium Priority
?
212 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 500 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 500 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

670 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