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

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!)

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.

Basically, all you have to do is

select ..., (select ... from ... where ...) as ..., ..., from ... where ...

For exemple. You can insert the (select ... from ... where) wherever you want it will be replaced by the corresponding data.

I know that the others exemple (even if each of them are really great :) ) are a bit complicated to understand for the newbies (like me :p) so i hope this "simple" exemple will help some of you guys :)

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'

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'

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