Oracle SQL - select within a select (on the same table!)

Question

Hi guys,

I'll try and explain what I'm trying to achieve quickly, since I have no idea how to explain it otherwise!

We have a table here that shows all employment history for all employees, I want the "Start_Date" of the current post ("Current_Flag" = 'Y'). As well as that, I want the "End_Date" of the post before that (was going to filter by current flag, sort by end date, and just grab the top one)

So anyway, here's my code:

SELECT "Gc_Staff_Number",
       "Start_Date",
       (SELECT "End_Date"
        FROM   "Employment_History"
        WHERE  "Current_Flag" != 'Y'
               AND ROWNUM = 1
               AND "Employee_Number" = "Employment_History"."Employee_Number"
        ORDER  BY "End_Date" ASC)
FROM   "Employment_History"
WHERE  "Current_Flag" = 'Y'

Any suggestions on how to get this working would be fantastic, hopefully the above makes a little bit of sense - to be honest the query at the moment won't even work which really sucks, hmm.

(edit: Oh! I'm writing this to query an existing system... which for some reason has all of the stupid double quotes around the table and field names, sigh!)

Answer 1

I'm a bit confused by the quotes, however, below should work for you:

SELECT "Gc_Staff_Number",
       "Start_Date", x.end_date
FROM   "Employment_History" eh,
(SELECT "End_Date"
        FROM   "Employment_History"
        WHERE  "Current_Flag" != 'Y'
               AND ROWNUM = 1
               AND "Employee_Number" = eh.Employee_Number
        ORDER  BY "End_Date" ASC) x
WHERE  "Current_Flag" = 'Y'

Answer 2

SELECT eh."Gc_Staff_Number",
       eh."Start_Date",
       MAX(eh2."End_Date") AS "End_Date"
FROM   "Employment_History" eh
LEFT JOIN  "Employment_History" eh2
ON eh."Employee_Number" = eh2."Employee_Number" and eh2."Current_Flag" != 'Y'
WHERE  eh."Current_Flag" = 'Y' 
GROUP BY eh."Gc_Staff_Number",
       eh."Start_Date

Answer 3

This is something I'd use the LAG function for:

SELECT eh.gc_staff_number,
       eh.start_date,
       LAG(eh.end_date) OVER (PARTITION BY eh.gc_staff_number
                                  ORDER BY eh.end_date) AS prev_end_date
  FROM EMPLOYMENT_HISTORY eh
 WHERE eh.current_flag = 'Y'

If you wanted to peek a row ahead, you'd use the LEAD function.

Compatibility:

To my knowledge, this is supported 9i+ but I haven't confirmed that 8i is supported like the documentation claims.

LEAD and LAG are finally ANSI, but only Oracle and PostgreSQL v8.4+ support them currently.

Answer 4

This is precisely the sort of scenario where analytics come to the rescue.

Given this test data:

SQL> select * from employment_history
  2  order by Gc_Staff_Number
  3             , start_date
  4  /

GC_STAFF_NUMBER START_DAT END_DATE  C
--------------- --------- --------- -
           1111 16-OCT-09           Y
           2222 08-MAR-08 26-MAY-09 N
           2222 12-DEC-09           Y
           3333 18-MAR-07 08-MAR-08 N
           3333 01-JUL-09 21-MAR-09 N
           3333 30-JUL-10           Y

6 rows selected.

SQL> 

An inline view with an analytic LAG() function provides the right answer:

SQL> select Gc_Staff_Number
  2             , start_date
  3             , prev_end_date
  4  from   (
  5      select Gc_Staff_Number
  6             , start_date
  7             , lag (end_date) over (partition by Gc_Staff_Number
  8                                    order by start_date )
  9                  as prev_end_date
 10             , current_flag
 11      from employment_history
 12  )
 13  where current_flag = 'Y'
 14  /

GC_STAFF_NUMBER START_DAT PREV_END_
--------------- --------- ---------
           1111 16-OCT-09
           2222 12-DEC-09 26-MAY-09
           3333 30-JUL-10 21-MAR-09

SQL>

The inline view is crucial to getting the right result. Otherwise the filter on CURRENT_FLAG removes the previous rows.

Answer 5

SELECT "Gc_Staff_Number",
       "Start_Date",
       (SELECT "End_Date"
        FROM   "Employment_History"
        WHERE  "Current_Flag" != 'Y'
               AND ROWNUM = 1
               AND "Employee_Number" = "Employment_History"."Employee_Number"
        ORDER  BY "End_Date" ASC)
FROM   "Employment_History"
WHERE  "Current_Flag" = 'Y'

FYI, the ROWNUM = 1 gets evaluated before the ORDER BY in this case, so that inner query will sort a grand total of (at most) one record.

If you really are looking for the earliest end_date for a given employee (where current_flag <> 'Y') is this what you're looking for?

SELECT "Gc_Staff_Number",
       "Start_Date",
       eh.end_date
  FROM "Employment_History" eh
       LEFT OUTER JOIN -- in case the current record is the only record...
       (SELECT "Employee_Number"
             , MIN("End_Date") as end_date
          FROM "Employment_History"
         WHERE "Current_Flag" != 'Y'
         GROUP BY "Employee_Number" 
       ) emp_end_date
          ON eh."Employee_Number" = emp_end_date."Employee_Number"
 WHERE eh."Current_Flag" = 'Y'