Find Nth Highest Salary

Select *
From Employee_Salary

OUTPUT
Emp_Id Emp_Name Salary
1 Mahmad 13500
2 Bharat 9000
3 Tausif 8000
4 Amol 8500
5 Rajkumar 24500
6 Pankaj 28200
7 Sameer 18000
8 Sunil 16800

Below query will give third highest salary employee

Select *
From (
select Emp_Id,Emp_Name,Salary,
Dense_Rank() Over (Order By Salary Desc) as Dense_Rank
from Employee_Salary
) as A
Where A.Dense_Rank = 3

OUTPUT
Emp_Id Emp_Name Salary Dense_Rank
7 Sameer 18000 3

Fill Dropdown using web service and javascript

Fill Dropdown using web service and javascript


1 .aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"

Inherits="WebServiceCallApp.Default" EnableEventValidation="false" %>


transitional.dtd">
< html xmlns="http://www.w3.org/1999/xhtml">
< head runat="server">
< title >Web Service call from client-side JavaScript

< script language="javascript" type="text/javascript">
function Button1_onclick() {
var StateList = document.getElementById("ddl_States");
var selectedStateId = StateList.options[StateList.selectedIndex].value;


MySampleService.GetCities(selectedStateId, OnComplete, OnError, OnTimeOut);
}

function OnComplete(arg)
{

var xmlDoc = new ActiveXObject("MSXML2.DomDocument");
xmlDoc.loadXML(arg.xml);
var items = xmlDoc.getElementsByTagName('City_Name');


var CityList = document.getElementById("ddl_Cities");

for (var count = CityList.options.length-1; count >-1; count--)
{
CityList.options[count] = null;
}


var textValue;
var optionItem;

for (var count = 0; count < items.length; count++)
{

optionItem = new Option(xmlDoc.getElementsByTagName('City_Name')(count).text,

xmlDoc.getElementsByTagName('City_Id')(count).text, false, false);
CityList.options[count] = optionItem;
}


}







function OnTimeOut(arg)
{
alert("timeOut has occured");
}

function OnError(arg)
{
alert("error has occured: " + arg._message);
}
< /script >

< /head >
< body >
< form id="form1" runat="server">





< table style="width: 100%">
< tr>
< td style="width: 19%" align="right">
States
< td style="width: 1%">
:
< td style="width: 100px">


< /tr >
< tr >
< td style="width: 19%" align="right">
Cities
< td style="width: 1%">
:
< td style="width: 100px">


< /tr >
< tr >
< td colspan="3">

/>
< /tr >
< /table>






2. .aspx.cs file

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;

namespace WebServiceCallApp
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlConnection cn = new SqlConnection("Data Source=MAHMAD\\SQLEXPRESS;Initial Catalog=

Emp;UId=sa;Password=sql;");
cn.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "select State_Id,State_Name from dbo.States Order By State_Name";

SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);

ddl_States.DataTextField = "State_Name";
ddl_States.DataValueField = "State_Id";
ddl_States.DataSource = ds;
ddl_States.DataBind();


SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = cn;
cmd1.CommandText = "select City_Id,City_Name from dbo.Cities where state_id=" +

Convert.ToInt32(ddl_States.SelectedValue) + " Order By City_Name";

SqlDataAdapter adp1 = new SqlDataAdapter(cmd1);
DataSet ds1 = new DataSet();
adp1.Fill(ds1);

ddl_Cities.DataTextField = "City_Name";
ddl_Cities.DataValueField = "City_Id";
ddl_Cities.DataSource = ds1;
ddl_Cities.DataBind();
}

}
protected void Button1_Click(object sender, EventArgs e)
{
string selectedVal = Request[ddl_Cities.UniqueID];
Response.Write(selectedVal);


}
}
}

3. .asmx.cs page

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data.SqlClient;
using System.Data;

///

///
/// Project: AJAX enabled Web Service
/// Company: semenoff, http://www.semenoff.dk
/// Author: Rostislav Semenov, http://www.semenoff.dk
/// Created: August 2007
///

[WebService(Namespace = "http://semenoff.dk/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService()]
public class MySampleService : System.Web.Services.WebService
{

public MySampleService()
{

//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
public string GetServerResponse(string callerName)
{
if(callerName== string.Empty)
throw new Exception("Web Service Exception: invalid argument");

return string.Format("Service responded to {0} at {1}", callerName,

DateTime.Now.ToString());
}

[WebMethod]
[System.Web.Script.Services.ScriptMethod(ResponseFormat =

System.Web.Script.Services.ResponseFormat.Xml)]
public DataSet GetCities(int StateId)
{
SqlConnection cn = new SqlConnection("Data Source=MAHMAD\\SQLEXPRESS;Initial Catalog=

Emp;UId=sa;Password=sql;");
cn.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "select City_Id,City_Name from dbo.Cities where state_id=" + StateId + " Order

By City_Name";

SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);

return ds;
}

}