Loading ...
Sorry, an error occurred while loading the content.

Need Queries for Getting First Five Highest Salary Details

Expand Messages
  • Rama Prem
    Hi, 1) I have a employee table where the details of empid, salary, deptid are stored. I want to return the first Five employees who are getting more salary.
    Message 1 of 4 , Jul 31 8:59 PM
    • 0 Attachment
      Hi,

      1) I have a employee table where the details of empid, salary, deptid are stored. I want to return the first Five employees who are getting more salary.
      Could you please tell me the query?.

      2) How to return the third highest salary from a table?.

      Regards,
      PremShankar






      Send instant messages to your online friends http://uk.messenger.yahoo.com

      [Non-text portions of this message have been removed]
    • Michael Weiss
      Hi Use the TOP n to get the answer to your first question. SELECT TOP 5 empid, salary FROM Employees ORDER BY salary DESC The answer to your second question is
      Message 2 of 4 , Aug 1, 2006
      • 0 Attachment
        Hi
        Use the TOP n to get the answer to your first question.
        SELECT TOP 5 empid, salary FROM Employees ORDER BY salary DESC

        The answer to your second question is a little trickier. What you need
        to do here is rank the employees by salary. It gets tricky because you
        will need to allow for ties, two to n employees with the same salary
        amount. Here I am assuming you are using SQL Server 2000 or 2005. First
        create a temp table -

        SELECT empRank = IDENTITY(int), i.empid, i.salary
        INTO #TempRank
        FROM Employees e
        WHERE 1=2

        Then insert your employee info ordering by salary descending -

        INSERT #TempRank(empid, salary)
        FROM Employees
        ORDER BY salary DESC

        Now if you do a simple select where empRank = 3 you will get the FIRST
        of the employees with a salary that is the third largest. To get ALL
        employees with a salary that is third largest you need to do something
        like this -

        SELECT i.Ranking, r.empid, r.salary
        FROM
        (SELECT Ranking = MIN(t.empRank), t.salary FROM #TempRank t GROUP BY
        t.salary) i, #TempRank r
        WHERE r.salary = i.salary
        ORDER BY i.Ranking

        DROP TABLE #TempRank

        This example is modeled on Ken Henderson's query on page 192 of his "The
        Guru's Guide to Transact-SQL" book.
        Hth,
        Michael




        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com
        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Rama Prem
        Sent: Monday, July 31, 2006 8:59 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Need Queries for Getting First Five Highest
        Salary Details

        Hi,

        1) I have a employee table where the details of empid,
        salary, deptid are stored. I want to return the first Five employees who
        are getting more salary.
        Could you please tell me the query?.

        2) How to return the third highest salary from a table?.

        Regards,
        PremShankar






        Send instant messages to your online friends
        http://uk.messenger.yahoo.com

        [Non-text portions of this message have been removed]







        Yahoo! Groups Links
      • Arnie Rowland
        Michael, Wouldn t it be that in your example you are retrieving the third row loaded into the table and NOT the third highest salary? It seems to me that it
        Message 3 of 4 , Aug 1, 2006
        • 0 Attachment
          Michael,

          Wouldn't it be that in your example you are retrieving the third row loaded
          into the table and NOT the third highest salary?

          It seems to me that it 'could' be that the third row just happens to have
          the same salary value as the first row, or that the third row just happens
          to have the same salary value as the second row.

          (Aside: I much rather use table variables than #Temp tables, and definitely
          prefer to explicitly create them rather than have them created as a result
          of a Zero row insert.)

          DECLARE @SalaryRanking table
          ( Rank int IDENTITY
          , Salary Decimal
          )

          INSERT INTO @SalaryRanking
          SELECT DISTINCT Salary FROM Employees
          ORDER BY Salary DESC

          Of course, at this point, to just retrieve the third highest Salary, one
          only has to look in the @SalaryRanking table.

          The following query will return all employees in the Employees table having
          a Salary equal to or above the third highest Salary level.

          SELECT
          s.Rank
          , e.empid
          , e.salary
          FROM @SalaryRanking s
          JOIN Employees e
          ON e.salary = s.salary
          WHERE s.Rank <= 3
          ORDER BY
          s.Rank

          - Arnie Rowland

          "I am a great believer in luck, and I find that the harder I work, the more
          I have of it." - Thomas Jefferson (1743-1826)


          -----Original Message-----
          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael Weiss
          Sent: Tuesday, August 01, 2006 9:51 AM

          <snip>

          Then insert your employee info ordering by salary descending -

          INSERT #TempRank(empid, salary)
          FROM Employees
          ORDER BY salary DESC

          Now if you do a simple select where empRank = 3 you will get the FIRST
          of the employees with a salary that is the third largest. To get ALL
          employees with a salary that is third largest you need to do something
          like this -



          Disclaimer - August 1, 2006
          This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
          This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


          [Non-text portions of this message have been removed]
        • Michael Weiss
          Hmmm...could be unless I was loading the table with salary in descending order. As for temp tables vrs table variables, I would go with table variables. As for
          Message 4 of 4 , Aug 1, 2006
          • 0 Attachment
            Hmmm...could be unless I was loading the table with salary in descending
            order. As for temp tables vrs table variables, I would go with table
            variables. As for creating temp tables explicitly rather than via a zero
            row insert you are right that creating them explicitly is better.

            -----Original Message-----
            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
            Sent: Tuesday, August 01, 2006 1:24 PM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: RE: [SQLQueriesNoCode] Need Queries for Getting First Five
            Highest Salary Details

            Michael,

            Wouldn't it be that in your example you are retrieving the third row
            loaded
            into the table and NOT the third highest salary?

            It seems to me that it 'could' be that the third row just happens to
            have
            the same salary value as the first row, or that the third row just
            happens
            to have the same salary value as the second row.

            (Aside: I much rather use table variables than #Temp tables, and
            definitely
            prefer to explicitly create them rather than have them created as a
            result
            of a Zero row insert.)

            DECLARE @SalaryRanking table
            ( Rank int IDENTITY
            , Salary Decimal
            )

            INSERT INTO @SalaryRanking
            SELECT DISTINCT Salary FROM Employees
            ORDER BY Salary DESC

            Of course, at this point, to just retrieve the third highest Salary, one
            only has to look in the @SalaryRanking table.

            The following query will return all employees in the Employees table
            having
            a Salary equal to or above the third highest Salary level.

            SELECT
            s.Rank
            , e.empid
            , e.salary
            FROM @SalaryRanking s
            JOIN Employees e
            ON e.salary = s.salary
            WHERE s.Rank <= 3
            ORDER BY
            s.Rank

            - Arnie Rowland

            "I am a great believer in luck, and I find that the harder I work, the
            more
            I have of it." - Thomas Jefferson (1743-1826)


            -----Original Message-----
            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael Weiss
            Sent: Tuesday, August 01, 2006 9:51 AM

            <snip>

            Then insert your employee info ordering by salary descending -

            INSERT #TempRank(empid, salary)
            FROM Employees
            ORDER BY salary DESC

            Now if you do a simple select where empRank = 3 you will get the FIRST
            of the employees with a salary that is the third largest. To get ALL
            employees with a salary that is third largest you need to do something
            like this -



            Disclaimer - August 1, 2006
            This email and any files transmitted with it are confidential and
            intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the
            named addressee you should not disseminate, distribute, copy or alter
            this email. Any views or opinions presented in this email are solely
            those of the author and might not represent those of Westwood
            Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken
            reasonable precautions to ensure no viruses are present in this email,
            the company cannot accept responsibility for any loss or damage arising
            from the use of this email or attachments.
            This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


            [Non-text portions of this message have been removed]




            Yahoo! Groups Links
          Your message has been successfully submitted and would be delivered to recipients shortly.