Home » Developer & Programmer » Application Express & MOD_PLSQL » Inline views (10g)
Inline views [message #355976] Tue, 28 October 2008 16:57 Go to next message
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 Go to previous message
Michel Cadot
Messages: 59170
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: ORA-25006 issue
Next Topic: page can not found
Goto Forum:
  


Current Time: Tue Sep 23 01:27:03 CDT 2014

Total time taken to generate the page: 0.07119 seconds