# SQL SERVER QUERY FOR MAXIMUM SALARY

### السؤال

• FROM A TABLE WITH OUT USING      MAX, WHERE CLAUSE
• تم تغيير النوع 27/شعبان/1432 05:20 ص
25/رجب/1432 04:12 م

### الإجابات

• CREATE

Table Salary

(

ID

int,

Salary

money

)

INSERT INTO Salary values (1,10000)

Insert INTO Salary values (2,20000000)

Insert INTO Salary values (3,300000)

Insert INTO Salary values (4,400000)

Insert INTO Salary values (5,600000)

DECLARE @Count INT,

@N

INT

CREATE

TABLE #TEMP

(

ID

int,

Salary

Money

)

INSERT

INTO #TEMP

SELECT

ID,Salary FROm Salary

SET

@Count = (Select COUNT(1) FROm #TEMP

)

SEt

@N = 0

WHILE

@N < @Count -1

BEGIN

DELETE

FROm #TEMP Where Salary = ( SElect MIN(Salary) FROm #TEMP

)

SET

@N = @N +1

END

SELECT

* from #TEMP

DROP

table #Temp

Mr.GMKREDDY
• تم وضع علامة كإجابة بواسطة 28/شعبان/1432 03:24 ص
28/شعبان/1432 03:24 ص
• create table salaries (empid int identity(1,1), salary int)

go

insert into salaries (salary) values (1000)

go

insert into salaries (salary) values (2000)

go

insert into salaries (salary) values (3000)

go

insert into salaries (salary) values (2400)

go

insert into salaries (salary) values (100)

go

insert into salaries (salary) values (200)

go

insert into salaries (salary) values (1050)

go

insert into salaries (salary) values (8000)

go

insert into salaries (salary) values (1000)

go

insert into salaries (salary) values (2000)

go

insert into salaries (salary) values (1000)

go

insert into salaries (salary) values (2000)

go

select top 1 s1.salary from salaries s1

inner join salaries s2 on s1.salary> s2.salary

and s1.empid not in (select s2.empid from salaries s1

inner join salaries s2 on s1.salary> s2.salary)

Mr.GMKREDDY
• تم وضع علامة كإجابة بواسطة 28/شعبان/1432 03:25 ص
28/شعبان/1432 03:25 ص

### جميع الردود

• order by desc? top?
- Vishal

SqlAndMe.com

26/رجب/1432 07:42 ص
• i need  only the  maximumum salary   ...not the remaining.?
26/رجب/1432 09:22 ص
• Below query can be used for the same, but if there are ties it will only return one of the rows..

SELECT Top 1 * From Table
Order By Salary DESC,

- Vishal

SqlAndMe.com

26/رجب/1432 09:34 ص
• can we have chance  with  out using   order by too.

this is   asked in   microsoft   technical round

Mr.GMKREDDY
26/رجب/1432 09:42 ص
• Hi,

The best way of solving this problem is using the new SQL Analytic functions in SQL Server

You can use First_Value() analytic function. It can be even used with PARTITION BY clause just like previous Window functions in T-SQL

14/شعبان/1432 01:31 م
• CREATE

Table Salary

(

ID

int,

Salary

money

)

INSERT INTO Salary values (1,10000)

Insert INTO Salary values (2,20000000)

Insert INTO Salary values (3,300000)

Insert INTO Salary values (4,400000)

Insert INTO Salary values (5,600000)

DECLARE @Count INT,

@N

INT

CREATE

TABLE #TEMP

(

ID

int,

Salary

Money

)

INSERT

INTO #TEMP

SELECT

ID,Salary FROm Salary

SET

@Count = (Select COUNT(1) FROm #TEMP

)

SEt

@N = 0

WHILE

@N < @Count -1

BEGIN

DELETE

FROm #TEMP Where Salary = ( SElect MIN(Salary) FROm #TEMP

)

SET

@N = @N +1

END

SELECT

* from #TEMP

DROP

table #Temp

Mr.GMKREDDY
• تم وضع علامة كإجابة بواسطة 28/شعبان/1432 03:24 ص
28/شعبان/1432 03:24 ص
• create table salaries (empid int identity(1,1), salary int)

go

insert into salaries (salary) values (1000)

go

insert into salaries (salary) values (2000)

go

insert into salaries (salary) values (3000)

go

insert into salaries (salary) values (2400)

go

insert into salaries (salary) values (100)

go

insert into salaries (salary) values (200)

go

insert into salaries (salary) values (1050)

go

insert into salaries (salary) values (8000)

go

insert into salaries (salary) values (1000)

go

insert into salaries (salary) values (2000)

go

insert into salaries (salary) values (1000)

go

insert into salaries (salary) values (2000)

go

select top 1 s1.salary from salaries s1

inner join salaries s2 on s1.salary> s2.salary

and s1.empid not in (select s2.empid from salaries s1

inner join salaries s2 on s1.salary> s2.salary)

Mr.GMKREDDY
• تم وضع علامة كإجابة بواسطة 28/شعبان/1432 03:25 ص
28/شعبان/1432 03:25 ص
• You can do below:

```select s1.Salary from Salaries as s1 where not exists(select * from Salaries as s2 where s2.Salary > s1.Salary);
```

And if there can be multiple rows with same max salary then do:

```select distinct s1.Salary from Salaries as s1 where not exists(select * from Salaries as s2 where s2.Salary > s1.Salary);
```

Umachandar, SQL Programmability Team, SQLTIPS - http://blogs.msdn.com/sqltips
04/شوال/1432 04:57 م