Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First, the query will return the <code>nth</code> <strong>lowest</strong> salary value. To return the <code>nth</code> highest salary value you must change <code>t.sal &lt;= sal</code> to <code>t.sal &gt;= sal</code>.</p> <p>Next, this query works by first finding the distinct list of salary values as one derived table and then determines the number of employees that have a salary less than each one in this list. <code>t.sal &lt;= sal</code> is taking the derived table (which most databases would require have an alias) and comparing each value against the outer <code>emp</code> table. It should be noted that this will return multiple rows in the case of a tie.</p> <p>To manually trace the output, we need some inputs:</p> <pre><code>Alice | 200 Bob | 100 Charlie | 200 Danielle | 150 Select Distinct sal From emp </code></pre> <p>Gives us</p> <pre><code>200 100 150 </code></pre> <p>Now we analyze each row in the outer table</p> <pre><code>Alice - There are 3 distinct salary values less than or equal to 200 Bob - 1 rows &lt;= 100 Charlie - 3 rows &lt;= 200 Danielle - 2 row &lt;= 150 </code></pre> <p>Thus, for each salary value we get the following counts (and reordered by count):</p> <pre><code>Bob 1 Danielle 2 Charlie 3 Alice 3 </code></pre> <p>The most important aspect that I think you are overlooking is that the outer <code>emp</code> table is <em>correlated</em> to the inner count calculation (which is why it is called a correlated subquery). I.e., for each row in the outer <code>emp</code> table, a new count is calculated for that row's salary via <code>t.sal &lt;= sal</code>. Again, most database systems would require the inner most query to have an alias like so (note the <code>As Z</code> alias):</p> <pre><code>Select sal From emp As t Where &amp;n = ( Select Count(Z.sal) From ( Select Distinct sal From emp ) As Z Where t.sal &lt;= Z.sal ) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL query to find Nth highest salary
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USThomas
    1. USThomas
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSQL query to find Nth highest salary
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks. However , its still not clear to me how following part will work ? where &n = (select count(sal) from.....) Wouldn't COUNT just return a number ? How it helps to select that particular row ?
      singulars
      1. PO
      1. USVishal
    2. CO@Vishal - Look at how the very last results pan out. The count of distinct salary values `<= 100` for `Bob` will be 1, Danielle: 2, Charlie: 3 and Alice: 3. Thus if you filter for say `n = 2`, you will get the `nth` **lowest** salary value. To get the `nth` **highest** salary value, you must change `t.sal <= sal` to `t.sal >= sal`
      singulars
      1. PO
      1. USThomas
    3. CO@Vishal - The inner query doing the `Select Count` is correlated to the outer query via `t.sal <= sal`. The `t.` part references the `emp` table in the outer most query. The `sal` part (without `t.`) references the inner query. Most RDMS would require that the derived table (starting with `Select Distinct Sal`) be aliased. (e.g. `(Select Distinct From emp) As Z`
      singulars
      1. PO
      1. USThomas
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload