Home » SQL & PL/SQL » SQL & PL/SQL » MIN MAX SUBQUERIES
MIN MAX SUBQUERIES [message #352968] Fri, 10 October 2008 06:14 Go to next message
jay_pink_elephant
Messages: 2
Registered: October 2008
Junior Member
hi, im trying to list youngest and eldest agents by location using min and max sub-queries. Problem is agents appear in both columns regardless of age:

LOCATION YOUNGEST ELDEST
Birmingham Pelton Magoon Pelton Magoon
Birmingham Tranter Toucey Tranter Toucey
Bournemouth Lily Beaufort Lily Beaufort
Bournemouth Ned Killin Ned Killin
Brighton Joanna Oakham Joanna Oakham
Brighton Newell Marmaduke Newell Marmaduke

Should look like:

LOCATION YOUNGEST ELDEST
Birmingham Tranter Toucey Pelton Magoon
Bournemouth Ned Killin Lily Beaufort
Brighton Newell Marmaduke Joanna Oakham

Heres the script, please help

SELECT
l.description Location,
a.first_name||' '||a.last_name Youngest,
a.first_name||' '||a.last_name Eldest
FROM
locations l
INNER JOIN
agents a
ON
l.location_id=a.location_id
WHERE
a.birth_date =
(
SELECT
MAX(a2.birth_date) as Youngest
FROM
agents a2
WHERE
a.location_id = a2.location_id)
OR
a.birth_date =
(
SELECT
MIN(a3.birth_date) as Eldest
FROM
agents a3
WHERE
a.location_id = a3.location_id
)
GROUP BY
l.description,
a.first_name||' '||a.last_name
ORDER BY
l.description
;
Re: MIN MAX SUBQUERIES [message #352971 is a reply to message #352968] Fri, 10 October 2008 06:42 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Better you try with Correlated Subquery

Thumbs Up
Rajuvan
Re: MIN MAX SUBQUERIES [message #352972 is a reply to message #352968] Fri, 10 October 2008 06:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on the data you've posted, it can't be done.
You need a column that will allow you to identify which row contains the value that is to be used for the Youngest, and which row is to be used for the Eldest.

Re: MIN MAX SUBQUERIES [message #352995 is a reply to message #352968] Fri, 10 October 2008 08:08 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
The part for the younger person is here, the part for the older You should complete:
 WITH a AS
     (  SELECT 'Birmingham' location_id,  'Pelton' first_name, 'Magoon'  last_name, '01011904' birth_date FROM DUAL
        UNION ALL 
        SELECT 'Birmingham' location_id, 'Tranter' first_name, 'Toucey'  last_name, '03011900' birth_date FROM DUAL
        UNION ALL
        SELECT 'Birmingham' location_id, 'Mucker'  first_name,  'Schnac' last_name, '02061900' birth_date FROM DUAL
        UNION ALL
        SELECT 'Bournemouth' location_id, 'Lily'   first_name,'Beaufort' last_name, '01081961' birth_date FROM DUAL
        UNION ALL
        SELECT 'Bournemouth' location_id, 'Ned'    first_name, 'Killin'  last_name, '13081961' birth_date FROM DUAL)
SELECT young.location_id LOCATION, young.NAME youngest
  FROM (SELECT location_id, first_name ||' '||last_name NAME
          FROM a WHERE (location_id,       birth_date)
          IN( SELECT  a.location_id, MAX(a.birth_date)
          FROM a, a a1 WHERE a.location_id = a1.location_id GROUP BY a.location_id)) young


I simply used strings for birth_date, much better is to use correct date types (min, max !)

Regards
JUM

[Updated on: Fri, 10 October 2008 08:15]

Report message to a moderator

Re: MIN MAX SUBQUERIES [message #353027 is a reply to message #352968] Fri, 10 October 2008 11:55 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

yes JRowbottom ,

I didn't notice it properly


@ jum ,

Output of the above Query . But its wrong !!!

LOCATION    YOUNGEST
----------- ----------------
Bournemouth Ned Killin
Birmingham  Tranter Toucey


For Birmingham , Youngest person is 'Pelton Magoon' ( 09-JAN-1904 ) and not 'Tranter Toucey' ( 03-JAN-1900 ) .

This is because Birthdate is treated as Character ('03011900') , and not DATE ('03011900','DDMMYYYY') .

DATE should be treated as DATE always

Thumbs Up
Rajuvan.

[Updated on: Fri, 10 October 2008 11:57]

Report message to a moderator

Re: MIN MAX SUBQUERIES [message #353108 is a reply to message #353027] Sat, 11 October 2008 08:35 Go to previous messageGo to next message
jay_pink_elephant
Messages: 2
Registered: October 2008
Junior Member
thanks for the replies. JRowbottom, still unclear as to this extra column i need, i thought the a2.location_id and a3.location_id would solve that issue?
Re: MIN MAX SUBQUERIES [message #353229 is a reply to message #353027] Mon, 13 October 2008 01:04 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
@rajavu1
as i mentioned: Smile
Quote:

I simply used strings for birth_date, much better is to use correct date types (min, max !)


If one uses the correct DATE types, the query gives the correct answer.
Previous Topic: join two tables with ip
Next Topic: DECODE QUERY
Goto Forum:
  


Current Time: Sat Dec 10 20:31:45 CST 2016

Total time taken to generate the page: 0.07203 seconds