locked
SQL SERVER QUERY FOR MAXIMUM SALARY

الإجابات

  • 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
    • تم وضع علامة كإجابة بواسطة 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
    • تم وضع علامة كإجابة بواسطة 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
    • تم وضع علامة كإجابة بواسطة 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
    • تم وضع علامة كإجابة بواسطة 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 م