Home » SQL & PL/SQL » SQL & PL/SQL » Select lower 5 and upper 5 records!!
Select lower 5 and upper 5 records!! [message #226583] Sun, 25 March 2007 05:50 Go to next message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
Hi,

Any help on a select statement to select 5 lower and 5 upper records based on a given criteria.

Thanks
Re: Select lower 5 and upper 5 records!! [message #226586 is a reply to message #226583] Sun, 25 March 2007 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at row_number, rank and dense_rank functions.

Regards
Michel

Re: Select lower 5 and upper 5 records!! [message #226587 is a reply to message #226583] Sun, 25 March 2007 06:35 Go to previous messageGo to next message
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 #226590 is a reply to message #226587] Sun, 25 March 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can do it wihout union.
You have to think of what happens if there is several rows with the same criteria value.

Regards
Michel
Re: Select lower 5 and upper 5 records!! [message #226630 is a reply to message #226590] Sun, 25 March 2007 23:52 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

with union how is it possible i dont know

u have any idea ?

tell me

Thanks,
srinivas
Re: Select lower 5 and upper 5 records!! [message #226631 is a reply to message #226630] Sun, 25 March 2007 23:54 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
No Message Body
Re: Select lower 5 and upper 5 records!! [message #226632 is a reply to message #226631] Sun, 25 March 2007 23:55 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry

without union how is it possible i dont know

u have any idea ?

tell me

Thanks,
Re: Select lower 5 and upper 5 records!! [message #226646 is a reply to message #226630] Mon, 26 March 2007 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 #226649 is a reply to message #226646] Mon, 26 March 2007 01:53 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i think any project

dont create any table like instance.

ok

Thanks,
srinivas
Re: Select lower 5 and upper 5 records!! [message #226650 is a reply to message #226649] Mon, 26 March 2007 01:59 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

i modified
see this
select empno ,ename,sal from (select empno, ename, sal,
row_number () over (order by sal) rn1,
row_number () over (order by sal desc) rn2
from emp
) where rn1<=5 or rn2<=5

thanks,
srinivas

Re: Select lower 5 and upper 5 records!! [message #226651 is a reply to message #226583] Mon, 26 March 2007 02:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #226661 is a reply to message #226656] Mon, 26 March 2007 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Asher,

Please read the previous posts before writing something:
1/ I previously said this can be done with either row_number, rank or dense_rank depending on your request.
2/ I previously said this can be done without an union (all)
3/ The OP said this is not what he wanted.

Regards
Michel
Re: Select lower 5 and upper 5 records!! [message #226666 is a reply to message #226661] Mon, 26 March 2007 02:31 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Yes you are correct.. I just didn't see the answers posted Embarassed
Re: Select lower 5 and upper 5 records!! [message #226684 is a reply to message #226651] Mon, 26 March 2007 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: Select lower 5 and upper 5 records!! [message #226964 is a reply to message #226684] Tue, 27 March 2007 00:34 Go to previous messageGo to next message
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 #226969 is a reply to message #226964] Tue, 27 March 2007 00:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why why why why why come to an Oracle forum if you have a SQLServer question???
And WHY WHY WHY didn't you say so 15 posts ago??
Re: Select lower 5 and upper 5 records!! [message #226988 is a reply to message #226969] Tue, 27 March 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have the answer, Frank: it is in his nickname!
We should think about it. Laughing

Regards
Michel
Re: Select lower 5 and upper 5 records!! [message #226998 is a reply to message #226583] Tue, 27 March 2007 01:45 Go to previous messageGo to next message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
I AM SORRYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
Re: Select lower 5 and upper 5 records!! [message #227004 is a reply to message #226998] Tue, 27 March 2007 01:59 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You gave me an idea for a new sticky.

MHE
Re: Select lower 5 and upper 5 records!! [message #227049 is a reply to message #226583] Tue, 27 March 2007 03:49 Go to previous messageGo to next message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
Sorry for creating trouble by posting in wrong forum. Btw I have solved my problem.

Thanks for the replies.

Re: Select lower 5 and upper 5 records!! [message #227052 is a reply to message #227049] Tue, 27 March 2007 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just by curiousity, can you post the solution.

Regards
Michel
Re: Select lower 5 and upper 5 records!! [message #227062 is a reply to message #227052] Tue, 27 March 2007 04:16 Go to previous messageGo to next message
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



Re: Select lower 5 and upper 5 records!! [message #227072 is a reply to message #227062] Tue, 27 March 2007 04:37 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks
Previous Topic: How do I expand the number of characters to be displayed on one line?
Next Topic: Exceptions
Goto Forum:
  


Current Time: Thu Dec 08 14:27:48 CST 2016

Total time taken to generate the page: 0.16593 seconds