join table for top 5 and bottom 5 [message #651825] |
Wed, 25 May 2016 18:08 |
|
nath123
Messages: 19 Registered: May 2016 Location: united states
|
Junior Member |
|
|
Hi
i have top 5 and bottom 5 queries how to join them , please suggest
I googled few things but could not achieve
WITH t AS
(
SELECT *
FROM (
SELECT salary,
Dense_rank() over (ORDER BY salary DESC) dr
FROM employees )
WHERE dr <=5 ) ,c AS
select *
FROM (
SELECT salary,
dense_rank() over (ORDER BY salary ASC) ar
FROM employees )
WHERE ar <=5
SELECT t.employee_id AS top5 ,
c.employee_id AS bottom5
FROM t,
c
WHERE t.dr =c.ar and order by t.salary ;
|
|
|
|
|
|
|
Re: join table for top 5 and bottom 5 [message #651832 is a reply to message #651825] |
Thu, 26 May 2016 00:08 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> select employee_id, salary
2 from ( select employee_id, salary,
3 dense_rank() over (order by salary asc) rk1,
4 dense_rank() over (order by salary desc) rk2
5 from employees )
6 where rk1 <= 5 or rk2 <= 5
7 order by 2, 1
8 /
EMPLOYEE_ID SALARY
----------- ----------
132 2100
128 2200
136 2200
127 2400
135 2400
119 2500
131 2500
140 2500
144 2500
182 2500
191 2500
118 2600
143 2600
198 2600
199 2600
201 13000
146 13500
145 14000
101 17000
102 17000
100 24000
21 rows selected.
|
|
|