Oracle SQL - select within a select on the same table

Question

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

User · Answer

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

User · Answer

This is precisely the sort of scenario where analytics come to the rescue   Given this test data   SQL gt  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 gt     An inline view with an analytic LAG   function provides the right answer   SQL gt  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 gt    The inline view is crucial to getting the right result   Otherwise the filter on CURRENT FLAG removes the previous rows

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt    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

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

Examples related to sql

Examples related to oracle

Examples related to select

Examples related to join