CODEWITHSUNDEEP
sqloracle-database
user3224907
user3224907

Reputation: 768

How can I add a column with numeric rank to the rows within a GROUP BY?

Given the table of employee data below:

Employee_id Employee_department Salary
100         ACCOUNTING          50000
200         SALES               75000
300         SALES               100000
400         ACCOUNTING          60000

Please help with a query to output the listing below, sequencing salary in descending order within each department where the highest salary is assigned sequence #1:

Employee_id Employee_department Salary Sequence
400         ACCOUNTING          60000  1
100         ACCOUNTING          50000  2
300         SALES               100000 1
200         SALES               75000  2

Thanks!

Upvotes: 0

Views: 1396

Answers (2)

Mihai
Mihai

Reputation: 26804

SELECT Employee_id, Employee_department, Salary,
RANK() WITHIN GROUP
   (ORDER BY Employee_department ASC, Salary DESC) "Sequence" 
FROM t

Ooops, WITHIN GROUP cant be used as an analytical,here is an alternative

SELECT Employee_id, Employee_department, Salary,
(SELECT COUNT(*)
          FROM t x 
         WHERE x.salary >= t.salary AND x.Employee_department =t.Employee_department ) AS  "Sequence"
FROM t
ORDER BY Employee_department ASC, Salary DESC

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26363

You can use analytical functions for this, specifically the RANK() function:

SELECT
  Employee_id,
  Employee_department,
  Salary,
  RANK() OVER (PARTITION BY Employee_department ORDER BY Salary DESC) AS Sequence
FROM myTable

The PARTITION performs the ranking by department; remove it and you'll get a ranking across all departments.

The RANK will include ties, so if there are two people who share the highest salary in the department they'll both rank #1, and the next-highest salary will rank #3. If you want the next-highest salary to rank #2 instead of #3, use DENSE_RANK.

Upvotes: 2

Related Questions