Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    64

    Thumbs down Unanswered: Dense_rank in SQL Server 2005

    Hi all ,

    I am trying to get SQL Server (2005) equivalent of Oracle's Dense_RANK syntax .
    I have the below Query in Oracle which is executable (details are here ):
    SELECT department_id,
    MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
    MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
    FROM employees
    GROUP BY department_id;
    I came to know that SQL Server 2005 does not support FIRST and LAST clauses .
    So i tried to reproduce the SQL Server equivalent by using a subquery in from clause but no success .

    Please help me

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Describe what you want, rather than posting the Oracle code. I'm sure there is a way to produce the result set in SQL Server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2007
    Posts
    64
    The above example returns, within each department of the sample table 'employees', the minimum salary among the employees who make the lowest commission and the maximum salary among the employees who make the highest commission .

    Dense_rank() will return the rank for the aggregate function MIN(salary), FIRST will take the very first row of the of this result set.

    LAST will return the last row of the result set

    For more details you can visit
    http://www.acs.ilstu.edu/docs/oracle...nctions048.htm

    I tried with Dense_rank function in SQL Server, but i don't know how to return FIRST and LAST rows

    Thanks ,
    Rajesh Reddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •