| sql help [message #225203] |
Mon, 19 March 2007 01:59  |
pavuluri
Messages: 247
Registered: January 2007
|
Senior Member |
|
|
i have emp table.
it has only 14 records
i want to display 5 to 8 records
select * from (select rownum rn ,e.* from (select * from emp) e
minus
select rownum rn ,e.* from (select * from emp)e where rownum<=4) where rn <=8
is this correct query.
any perform isues is there in this query.
|
|
|
|
| Re: sql help [message #225205 is a reply to message #225203] |
Mon, 19 March 2007 02:35  |
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
|
Senior Member |
|
|
hi,
select e1.* from (
select e.*,rownum r from emp e order by empno)e1
where e1.r>=5 and e1.r<=8
regards,
|
|
|
|
|
|
| Re: sql help [message #225217 is a reply to message #225205] |
Mon, 19 March 2007 03:29 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
@Dhananjay - schoolboy error I'm afraid. Your query fails, because the results of the ROWNUM occur before the ORDER BY does.
If you ue an example like the one below where the employees are not inserted in a numberically increasing order,the problem becomes easy to see:create table emp (empno number, ename varchar2(30));
insert into emp values (10,'Employee 10');
insert into emp values (12,'Employee 12');
insert into emp values (5,'Employee 5');
insert into emp values (7,'Employee 7');
insert into emp values (17,'Employee 17');
insert into emp values (1,'Employee 1');
insert into emp values (20,'Employee 20');
insert into emp values (18,'Employee 18');
insert into emp values (6,'Employee 6');
insert into emp values (3,'Employee 3');
insert into emp values (11,'Employee 11');
insert into emp values (4,'Employee 4');
insert into emp values (8,'Employee 8');
insert into emp values (9,'Employee 9');
insert into emp values (13,'Employee 13');
SQL> -- The wrong answer
SQL> select e1.* from (
2 select e.*,rownum r from emp e order by empno)e1
3 where e1.r>=5 and e1.r<=8;
EMPNO ENAME R
---------- ------------------------------ ----------
1 Employee 1 6
17 Employee 17 5
18 Employee 18 8
20 Employee 20 7
SQL> -- The correct version of the previous query
SQL> select e1.*
2 from ( select e.*
3 ,rownum r
4 from (select *
5 from emp
6 order by empno ) e
7 ) e1
8 where e1.r>=5
9 and e1.r<=8;
EMPNO ENAME R
---------- ------------------------------ ----------
6 Employee 6 5
7 Employee 7 6
8 Employee 8 7
9 Employee 9 8
SQL> -- another way of doing it
SQL> select e1.*
2 from (select e.*
3 ,row_number() over (order by empno) r
4 from emp e) e1
6 where e1.r>=5
7 and e1.r<=8;
EMPNO ENAME R
---------- ------------------------------ ----------
6 Employee 6 5
7 Employee 7 6
8 Employee 8 7
9 Employee 9 8
{removed surplus ORDER BY from final SQL]
[Updated on: Mon, 19 March 2007 05:32] Report message to a moderator |
|
|
|
| Re: sql help [message #225229 is a reply to message #225217] |
Mon, 19 March 2007 04:26 |
pavuluri
Messages: 247
Registered: January 2007
|
Senior Member |
|
|
i want last 10 records.
select * from (select e1.*
from (select e.*, row_number() over ( order by empno ) as rn from emp e order by empno ) e1 order by e1.rn desc)
where rownum<=10
is this query is right.
any problem in rownum.
|
|
|
|
| Re: sql help [message #225232 is a reply to message #225229] |
Mon, 19 March 2007 04:32 |
pavuluri
Messages: 247
Registered: January 2007
|
Senior Member |
|
|
hi,
in those queries which is correct.
select * from (select * from emp order by rownum desc) where rownum<10
select * from (select e1.*
from (select e.*, row_number() over ( order by empno ) as rn from emp e order by empno ) e1 order by e1.rn desc)
where rownum<=10
thanks,
srinivas
|
|
|
|
|
|
| Re: sql help [message #225247 is a reply to message #225232] |
Mon, 19 March 2007 05:31 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you notice, I've given you a set of table creation scripts and insert statements (and that has GOT to be the wrong way round.
Why don't you runthe queries yourself and see what happens?
I wouldn't use either of your queries - I'd simply change the order by in the ROW_NUMBER statement.
select e1.*
from (select e.*, row_number() over ( order by empno desc ) as rn
from emp e) e1
where rn<=10;
{edited for typso]
[Updated on: Mon, 19 March 2007 05:45] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
