Here’s a sample SQL query to print rank by using Over and Partition By in the DB2 SQL query
Print Rank: Over Partition By
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester8 rows selected.
SQL>
SQL> SELECT
2 city, SUM(salary),
3 RANK() OVER (PARTITION BY city ORDER BY SUM(salary) DESC) AS rank
4 FROM employee
5 GROUP BY city
6 ORDER BY city;
CITY SUM(SALARY) RANK
---------- ----------- ----------
New York 12220.56 1
Toronto 1234.56 1
Vancouver 19118.9 1Related
Srinimf 