| Inline views [message #355976] |
Tue, 28 October 2008 16:57  |
rob_chaplin@hotmail.co.uk
Messages: 1
Registered: October 2008
|
Junior Member |
|
|
The following query displays the youngest person at each location. How would Iammend this to show the youngest AND the oldest person at each location? (Schema attached)
SELECT
locbd.loc,locbd.loc LOCATION
youngest.first_name||' '||youngest.last_name Youngest
FROM
(
SELECT
l.location_id loc,
MAX(a.birth_date) bd
FROM
locations l
INNER JOIN
agents a
ON
l.location_id=a.location_id
GROUP BY
l.location_id
)locbd
INNER JOIN
agents a
ON
a.birth_date = locbd.bd
;
***any advice greatly appreciated, Rob***
|
|
|
|
| Re: Inline views [message #356000 is a reply to message #355976] |
Tue, 28 October 2008 23:47  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
