Thanks for the question, Swarag.
Asked: July 25, 2018 - 6:24 pm UTC
Last updated: July 26, 2018 - 3:58 am UTC
Version: Oracle 11g (11.2.0.1.0)
Viewed 1000+ times
You Asked
Dear Tom,
Thanks for this wonderful platform where there is always opportunity to learn something new on things we think we already know. Really appreciate it.
I have a query regarding the analytical functions in Oracle. Query is based on the Employees table in HR schema.
This is the query
select employee_id,salary,rownumb,dnsrnk
from (select e.*,
row_number() over(partition by e.salary order by e.employee_id desc) rownumb,
dense_rank() over(order by salary desc) dnsrnk
from employees e where e.department_id = 80)
where dnsrnk = 7;
SQL> select employee_id,salary,rownumb,dnsrnk
2 from (select e.*,
3 row_number() over(partition by e.salary order by e.employee_id desc) rownumb,
4 dense_rank() over(order by salary desc) dnsrnk
5 from employees e where e.department_id = 80)
6 /
EMPLOYEE_ID SALARY ROWNUMB DNSRNK
----------- ---------- ---------- ----------
145 14000.00 1 1
146 13500.00 1 2
207 12000.00 1 3
147 12000.00 2 3
168 11500.00 1 4
174 11000.00 1 5
148 11000.00 2 5
162 10500.00 1 6
149 10500.00 2 6
169 10000.00 1 7
156 10000.00 2 7
150 10000.00 3 7
170 9600.00 1 8
...
But the following query is not returning anything
select employee_id,salary,rownumb,dnsrnk
from (select e.*,
row_number() over(partition by e.salary order by e.employee_id desc) rownumb,
dense_rank() over(order by salary desc) dnsrnk
from employees e where e.department_id = 80)
where dnsrnk = 7;
What am i doing wrong here?
and Connor said...
I suspect perhaps an issue with 11.2.0.1 because I can't reproduce that in 11.2.0.4
I ran your queries unaltered, I just stripped off the right-part of the output to keep it from being too wide
SQL> select e.*,
2 row_number() over(partition by e.salary order by e.employee_id desc) rownumb,
3 dense_rank() over(order by salary desc) dnsrnk
4 from hr.employees e
5 where e.department_id = 80;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_D
----------- -------------------- ------------------------- ------------------------- -------------------- ------
145 John Russell JRUSSEL 011.44.1344.429268 01-OCT
146 Karen Partners KPARTNER 011.44.1344.467268 05-JAN
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR
168 Lisa Ozer LOZER 011.44.1343.929268 11-MAR
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-NOV
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-MAR
156 Janette King JKING 011.44.1345.429268 30-JAN
150 Peter Tucker PTUCKER 011.44.1344.129268 30-JAN
170 Tayler Fox TFOX 011.44.1343.729268 24-JAN
163 Danielle Greene DGREENE 011.44.1346.229268 19-MAR
157 Patrick Sully PSULLY 011.44.1345.929268 04-MAR
151 David Bernstein DBERNSTE 011.44.1344.345268 24-MAR
158 Allan McEwen AMCEWEN 011.44.1345.829268 01-AUG
152 Peter Hall PHALL 011.44.1344.478968 20-AUG
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-MAR
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-APR
159 Lindsey Smith LSMITH 011.44.1345.729268 10-MAR
153 Christopher Olsen COLSEN 011.44.1344.498718 30-MAR
160 Louise Doran LDORAN 011.44.1345.629268 15-DEC
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 09-DEC
171 William Smith WSMITH 011.44.1343.629268 23-FEB
172 Elizabeth Bates EBATES 011.44.1343.529268 24-MAR
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-JAN
161 Sarath Sewall SSEWALL 011.44.1345.529268 03-NOV
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-NOV
165 David Lee DLEE 011.44.1346.529268 23-FEB
166 Sundar Ande SANDE 011.44.1346.629268 24-MAR
179 Charles Johnson CJOHNSON 011.44.1644.429262 04-JAN
167 Amit Banda ABANDA 011.44.1346.729268 21-APR
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-APR
34 rows selected.
SQL> select employee_id,salary,rownumb,dnsrnk
2 from (
3 select e.*,
4 row_number() over(partition by e.salary order by e.employee_id desc) rownumb,
5 dense_rank() over(order by salary desc) dnsrnk
6 from hr.employees e
7 where e.department_id = 80
8 )
9 where dnsrnk = 7;
EMPLOYEE_ID SALARY ROWNUMB DNSRNK
----------- ---------- ---------- ----------
169 10000 1 7
156 10000 2 7
150 10000 3 7
Hope this helps.