title: How to find the nth largest salary by department using SQLite
body: <p>Say all the employees are listed in the same table <code>employees</code> along with their departments. How would you find the nth largest salary by department?</p>
  <p>I know <a href="https://stackoverflow.com/questions/80706/query-to-find-n%E1%B5%97%CA%B0-max-value-of-a-column">this</a> gets you pretty close.</p>
  <pre><code>select SAL from EMPLOYEE E1 where 
   (N - 1) = (select count(distinct(SAL)) 
              from EMPLOYEE E2 
              where E2.SAL &gt; E1.SAL )
  </code></pre>
  <p>But adding groupby still wouldn't get you there unless you did a join on the condition?</p>
  <pre><code>select SAL from EMPLOYEE E1
  
  where 
   (N - 1) = (select count(distinct(SAL)) 
              from EMPLOYEE E2 
              inner join EMPLOYEE E3 ON department_id
              where E2.SAL &gt; E3.SAL )
  groupby department
  </code></pre>