|
|
Re: Select lower 5 and upper 5 records!! [message #226587 is a reply to message #226583] |
Sun, 25 March 2007 06:35   |
pavuluri
Messages: 247 Registered: January 2007
|
Senior Member |
|
|
select e.*,rownum from emp e where rownum<=5
union
select * from (select e.*,row_number( ) over( order by empno desc) r from emp e) where r <=5
select * from (select * from emp order by empno desc) where rownum<=5
union
select * from emp where rownum<=5
thnaks
srinivas
|
|
|
|
|
|
|
Re: Select lower 5 and upper 5 records!! [message #226646 is a reply to message #226630] |
Mon, 26 March 2007 01:37   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
For instance,
SQL> with data as (
2 select empno, ename, sal,
3 row_number () over (order by sal) rn1,
4 row_number () over (order by sal desc) rn2
5 from emp
6 )
7 select empno, ename, sal, rn1, rn2
8 from data
9 where rn1 <= 5 or rn2 <= 5
10 order by sal
11 /
EMPNO ENAME SAL RN1 RN2
---------- ---------- ---------- ---------- ----------
7369 SMITH 800 1 12
7900 JAMES 950 2 11
7521 WARD 1250 3 10
7654 MARTIN 1250 4 9
7934 MILLER 1300 5 8
7782 CLARK 2450 8 5
7698 BLAKE 2850 9 4
7566 JONES 2975 10 3
7902 FORD 3000 11 2
7839 KING 5000 12 1
10 rows selected.
Regards
Michel
|
|
|
|
|
Re: Select lower 5 and upper 5 records!! [message #226651 is a reply to message #226583] |
Mon, 26 March 2007 02:07   |
SilyGuy
Messages: 15 Registered: May 2005
|
Junior Member |
|
|
I think I wasn't very clear my question. Here is what I want.
Name Salary
A 3000
B 2500
C 4000
D 2000
E 6000
F 5000
G 9000
H 3750
I 8450
.... ......
Output that I want is based on criteria such as Name=E and Salary=6000. I want to display names and salary of those 5 who get less salary than 6000 and 5 names of those who get more salary than 6000. So the total output will 11 records, salary 6000 will be in middle.
Hope this time i am clear.
Thanks
|
|
|
Re: Select lower 5 and upper 5 records!! [message #226656 is a reply to message #226583] |
Mon, 26 March 2007 02:14   |
asherisfine
Messages: 63 Registered: June 2006 Location: India
|
Member |
|
|
Hi you can use a dense_rank function along with a Union operation to get the required data
select * from (select empno,sal,dense_rank() over (order by sal asc) as tt from emp) where tt between 1 and 5
union
select * from (select empno,sal,dense_rank() over (order by sal desc) as tt from emp) where tt between 1 and 5
The first select gets you the first 5 records having lowest salary
The second select gets you the last 5 records having highest salary
|
|
|
|
|
|
Re: Select lower 5 and upper 5 records!! [message #226964 is a reply to message #226684] |
Tue, 27 March 2007 00:34   |
SilyGuy
Messages: 15 Registered: May 2005
|
Junior Member |
|
|
Michel Cadot wrote on Mon, 26 March 2007 04:33 | SilyGuy,
Here's one way to do it (I used 3 instead of 5 and 'ALLEN' name as the criteria):
SQL> with
2 data as (
3 select ename, sal,
4 row_number() over (order by sal) rn
5 from emp
6 )
7 select d1.ename, d1.sal, d1.rn
8 from data d1, data d2
9 where d2.ename = 'ALLEN'
10 and d1.rn between d2.rn-3 and d2.rn+3
11 order by d1.sal
12 /
ENAME SAL RN
---------- ---------- ----------
MARTIN 1250 4
MILLER 1300 5
TURNER 1500 6
ALLEN 1600 7
CLARK 2450 8
BLAKE 2850 9
JONES 2975 10
7 rows selected.
Regards
Michel
|
This seems to be good. But how can I do this in SQL Server. There is no row_number() function in SQLServer.
|
|
|
|
|
|
|
|
|
Re: Select lower 5 and upper 5 records!! [message #227062 is a reply to message #227052] |
Tue, 27 March 2007 04:16   |
SilyGuy
Messages: 15 Registered: May 2005
|
Junior Member |
|
|
select * from (
select * from (Select top 6 name ,salary from emp where salary<=6000 order by salary desc) As tmptbl1
union
select * from Select top 6 name ,salary from emp where salary>=6000 order by salary asc ) As tmptbl2
) as tmptbl3 order by salary
|
|
|
|