Find Nth Highest Salary of Employee

-
This is most popular question and see how can we solve this. There may be many ways to achieve this. Bur here i am providing solution in two different ways below,


1. First we will go with using simple sub-query.


SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
       ORDER BY salary DESC
)
ORDER BY salary

where n > 1 (n is always greater than one)

Below is the example to get the 2nd highest salary of employee using above generic query,
SELECT TOP 1 salary
FROM ( 
      SELECT DISTINCT TOP 2 salary 
      FROM employee
      ORDER BY salary DESC
     )
ORDER BY salary


2.
SELECT * 
FROM Employee E1
WHERE (N-1) = ( 
            SELECT COUNT(DISTINCT(E2.Salary))
            FROM Employee E2
            WHERE E2.Salary > E1.Salary
              )
Where N is the level of Salary to be determined.
In the above example, the inner query uses a value of the outer query in its filter condition.i.e., the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row.

Comments

Post a Comment

Popular posts from this blog

Auto Scroll in Common Controls

-
This example shows, how to programatically autoscroll WinForm controls TextBox, ListBox, ListView, TreeView and DataGridView. Autoscroll to the end of box or view is useful. For example, in situations when you use one of these components as the output log window. Usually you add the items to the box or view and you want to be sure that the recently added item is visible without the necessary to do it manually. Though it is not difficult to solve this problem, .NET doesn't provide any unified way how to do it. TextBox autoscroll Code: textBox1.SelectionStart = textBox1.Text.Length; textBox1.ScrollToCaret(); ListBox autoscroll Code: listBox1.SelectedIndex = listBox1.Items.Count - 1; listBox1.SelectedIndex = -1; ListView autoscroll Code: listView1.EnsureVisible(listView1.Items.Count - 1); TreeView autoscroll Code: treeView1.Nodes[treeView1.Nodes.Count - 1].EnsureVisible(); DataGridView autoscroll Code: dataGridView1.FirstDisplayedCell = ...
Read more

Convert typed library (.tlb) to .net assembly?

-
Sometimes we have to use the legacy code(C, C++) in .Net projects which is provided by third party vendors. Most of the times these guys will provide, equivalent .net framework typed library (.tlb) of that legacy code. Then, how to refer those in our .net projects and make use of functionality provided by it?   Follow the below steps, Keep ".tlb" file which is nothing but legacy typed library Open windows command prompt Go to directory, " C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin " or     "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin". It depends on which version of visual studio you had. Enter the following and execute, TlbImp.exe "E\MyLibrary.tlb" . Here, replace "E:\MyLibrary.tlb" with path to your tlb file. It will import the .net framework type library to assembly.i.e, .dll and it can be found at path "C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin" with name ...
Read more

Disable close button on form - C#

-
In one of my projects, I had to implement a form with Close[X] button Hide, so that the user cannot leave the form until it finishes the data processing. From the form designer window in VS.Net 2005, it is possible to hide the Minimize box and Maximize box. But there is no property called Close or CloseBox. Then I had tried with by setting form property ControlBox = false . With this i am able to achieve hiding the Close/Minimize/Maximize buttons. But at the same time there is a side effect.i.e., Form icon(which is generally displayed on form title bar)  also hidden.  So after little bit investigation came up with the following hack to get,  i)  disable the close button  ii) displaying the form icon To achieve this, we have to override CreateParams property of Form base class as follows, Code: private const int CP_DISABLE_CLOSE_BUTTON = 0x200; protected override CreateParams CreateParams { get ...
Read more