CODEWITHSUNDEEP
sqloracle-databaseselectjoin
Nick
Nick

Reputation: 1243

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

Upvotes: 8

Views: 139410

Answers (6)

N.K
N.K

Reputation: 1690

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332691

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.

Upvotes: 1

Patrick Marchand
Patrick Marchand

Reputation: 3445

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'

Upvotes: 2

APC
APC

Reputation: 146309

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.

Upvotes: 10

Martin Smith
Martin Smith

Reputation: 453707

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

Upvotes: 2

El Guapo
El Guapo

Reputation: 5781

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'

Upvotes: 2

Related Questions