title: Oracle SQL - select within a select (on the same table!)
body: <p>I'll try and explain what I'm trying to achieve quickly, since I have no idea how to explain it otherwise!</p>
  
  <p>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)</p>
  
  <p>So anyway, here's my code:</p>
  
  <pre><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'
  </code></pre>
  
  <p>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.</p>
  
  <p>(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!)</p>