Home » SQL & PL/SQL » SQL & PL/SQL » sql help
sql help [message #225203] Mon, 19 March 2007 01:59 Go to next message
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 Go to previous messageGo to next message
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 #225208 is a reply to message #225205] Mon, 19 March 2007 02:53 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
in both quries
which is fast
Thanks,
srinivas
Re: sql help [message #225217 is a reply to message #225205] Mon, 19 March 2007 03:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #225236 is a reply to message #225229] Mon, 19 March 2007 04:37 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


thanks JRowbottom for correcting.i was too lazy to check before posting.



regards,
Re: sql help [message #225247 is a reply to message #225232] Mon, 19 March 2007 05:31 Go to previous message
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

Previous Topic: Zero Divide
Next Topic: Query regarding Decode() function
Goto Forum:
  


Current Time: Fri Dec 09 19:33:35 CST 2016

Total time taken to generate the page: 0.10582 seconds