How to find Nth Highest salary in SQL server ?

In this post I wrote sql qureis to find Highest Salary, Second Highest, Third Highest ,Lowest salary and Nth Highest salary from EmpMaster table.

First I will create a database table in sql:-

CREATE TABLE [dbo].[tblEmpMaster](

       [EmpID] [int] NOT NULL,

       [EmpName] [varchar](100) NOT NULL,

       [EmpSalary] [int] NULL,

       [Age] [int] NULL

     )

Let me Insert some records in above table:-

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (1, N'Ram', 50000, 28)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (2, N'Naveen', 22000, 25)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (3, N'Neetu Singh', 17000, 27)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (5, N'R. P Singh', 62000, 23)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (6, N'Mohit', 27000, 24)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (6, N'Amit', 15000, 26)

GO

Now Records in Above table are:-

Query :-

SELECT [EmpID]

      ,[EmpName]

      ,[EmpSalary]

      ,[Age]

  FROM [Employee].[dbo].[tblEmpMaster]

Result:-

1. Highest EmpSalary using Aggregate Max() function

Select Max(EmpSalary) from [dbo].[tblEmpMaster]

2. Second Highest EmpSalary using Aggregate Max() function

Select Max(EmpSalary) from [dbo].[tblEmpMaster] where EmpSalary not in(Select Max(EmpSalary) from [dbo].[tblEmpMaster])

3.Highest EmpSalary using TOP  Clause

Select TOP 1 EmpSalary from [dbo].[tblEmpMaster] order by EmpSalary desc

4. Third Highest EmpSalary using TOP  Clause

Select TOP 1 EmpSalary from(Select TOP 3 EmpSalary from [dbo].[tblEmpMaster]

 order by EmpSalary desc)t order by EmpSalary asc

5. Third Lowest EmpSalary using TOP  Clause

Select TOP 1 EmpSalary from(Select TOP 3 EmpSalary from [dbo].[tblEmpMaster]

 order by EmpSalary asc)t order by EmpSalary desc

6. Now finally here I wrote a sql query to find Nth salary

Select * from [dbo].[tblEmpMaster] t1 where N-1=(Select Count(Distinct(EmpSalary))

 from [dbo].[tblEmpMaster] t2 where t2.EmpSalary>t1.EmpSalary)

Suppose If I will pass N=3 the the above query become like

Select * from [dbo].[tblEmpMaster] t1 where 2=(Select Count(Distinct(EmpSalary))

 from [dbo].[tblEmpMaster] t2 where t2.EmpSalary>t1.EmpSalary)

And it will find the third Highest salary from the table see result below

In the above article I try to explain the Mostly asked sql queries In interviews with example. I hope its useful to you. Please post your feedback, question, or comments about this article.

RowDataBound Event in GridView with example

In this post I am going to explain only RawDataBound event. If you want learn more GridView evnets then please see my previous post.

First I will create a database table in SQL and one Stored Procedure to fetch data.

Creating table:-

CREATE TABLE [dbo].[tblEmpMaster](

       [EmpID] [int] NOT NULL,

       [EmpName] [varchar](100) NOT NULL,

       [EmpSalary] [int] NULL,

       [Age] [int] NULL

)

Let me Insert some records into above table:-

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (1, N'Ram', 50000, 28)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (2, N'Naveen', 22000, 25)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (3, N'Neetu Singh', 17000, 27)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (5, N'R. P Singh', 62000, 23)

GO

INSERT [dbo].[tblEmpMaster] ([EmpID], [EmpName], [EmpSalary], [Age])

 VALUES (6, N'Mohit', 27000, 24)

GO

Now I will Create a Stored Procedure to fetch data from above table :-

CREATE Procedure [dbo].[getEmployee]

as

begin

SELECT [EmpID],[EmpName],[EmpSalary],[Age] FROM [Employee].[dbo].[tblEmpMaster]

end

Database part is over. Now let me come to GridView part:-

Aspx file

<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">

    <h2><%: Title %>.</h2>

    <h3>Demp Application</h3>

    <p>Grid View:- Insert, Edit, Delete Events</p>

    <div>

<asp:GridView ID="EmpGridView" Width="700px" runat="server" AutoGenerateColumns="False"

    DataKeyNames="EmpId" OnRowDataBound="EmpGridView_RowDataBound" >  

    <Columns>

        <asp:BoundField DataField="EmpId" HeaderText="Id"/> 

        <asp:BoundField DataField="EmpName" HeaderText="Name"/> 

        <asp:BoundField DataField="EmpSalary" HeaderText="Slary"/> 

        <asp:BoundField DataField="Age" HeaderText="Age"/>

    </Columns>

    </asp:GridView>

    </div>

</asp:Content>

Design:-

CS file

Connection String and Sql Conncetion Instances

static String conStr = WebConfigurationManager.ConnectionStrings["TestWebCon"].ConnectionString.ToString();

        static SqlConnection con = new SqlConnection(conStr);

PageLoad Method:-

       protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                bingGrid();

            }

        }

GridView bind method:-

public void bingGrid()

     {

            SqlCommand cmd = new SqlCommand("[dbo].[getEmployee]", con);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();

            da.Fill(ds);

         if(ds.Tables[0].Rows.Count>0)

         {

            EmpGridView.DataSource = ds.Tables[0];

            EmpGridView.DataBind();

           

         }

         else

         {

             ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());

             EmpGridView.DataSource = ds.Tables[0];

             EmpGridView.DataBind();

             int columncount = EmpGridView.Rows[0].Cells.Count;

             EmpGridView.Rows[0].Cells.Clear();

             EmpGridView.Rows[0].Cells.Add(new TableCell());

             EmpGridView.Rows[0].Cells[0].ColumnSpan = columncount;

             EmpGridView.Rows[0].Cells[0].Text = "No Records Found";

             con.Close();

         }

        

      }

RowDataBound Event:- 

In the below event I am checking employee salary if it is less then 

30000 k then I changed the row color as brown.

     protected void EmpGridView_RowDataBound(object sender, GridViewRowEventArgs e)

     {

         //Checking the RowType of the Row 

         if (e.Row.RowType == DataControlRowType.DataRow)

         {

             //If Salary is less than 10000 than set the row Background Color to Cyan

             if (Convert.ToInt64(e.Row.Cells[2].Text) < 30000)

             {

                 e.Row.BackColor = System.Drawing.Color.RosyBrown;

             }    

         }

     }  

Noe let me run my app and see result:-

In the above article I try to explain the RowdataBound Events of GridView in asp.net c# with example. I hope its useful to you. Please post your feedback, question, or comments about this article.