Home » SQL & PL/SQL » SQL & PL/SQL » query for using rownum and order by inthe same query
query for using rownum and order by inthe same query [message #262804] Tue, 28 August 2007 05:27 Go to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Hi All,
I have a problem. I have to get first 3 records from a queue order by the time they reach the queue. what should be the code for that?


select id from queue where id in(select id from queue order by enq_time) and rownum in(1,2,3);

But I got the error that
ERROR at line 1:
ORA-00907: missing right parenthesis


Please provide me the code.

Thanks and Regards
Sunil Gaurav
Re: query for using rownum and order by inthe same query [message #262811 is a reply to message #262804] Tue, 28 August 2007 05:36 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

search for analytical functions.
(hint row_number)



regards,
Re: query for using rownum and order by inthe same query [message #262812 is a reply to message #262804] Tue, 28 August 2007 05:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Move the 'IN' part to the FROM:
(warning! untested code!)
select id 
from   ( select id 
         from   queue 
         order by enq_time
       )
where  rownum <= 3;


MHE
Re: query for using rownum and order by inthe same query [message #262819 is a reply to message #262804] Tue, 28 August 2007 05:42 Go to previous messageGo to next message
jadoo83
Messages: 26
Registered: October 2006
Location: Mumbai
Junior Member
Check this.
select id from queue where rownum < 4 order by enq_time;
Re: query for using rownum and order by inthe same query [message #262820 is a reply to message #262819] Tue, 28 August 2007 05:44 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Dude, read up on Top N analysis. This will not get the top 3 values
Re: query for using rownum and order by inthe same query [message #262821 is a reply to message #262820] Tue, 28 August 2007 05:49 Go to previous messageGo to next message
jadoo83
Messages: 26
Registered: October 2006
Location: Mumbai
Junior Member
Yes, you are right.
Re: query for using rownum and order by inthe same query [message #262826 is a reply to message #262820] Tue, 28 August 2007 06:00 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
I have not yet got any correct solution..
Sad
Re: query for using rownum and order by inthe same query [message #262846 is a reply to message #262826] Tue, 28 August 2007 06:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
gauravsunil wrote on Tue, 28 August 2007 13:00
I have not yet got any correct solution..
Sad


Really?

My test script:
-- first 3 records from a queue order by the time they reach the queue.
CREATE TABLE yourtable(the_id NUMBER, in_date DATE)
/

INSERT INTO yourtable VALUES ( 1, TO_DATE ('3/8/2007 13:40', 'mm/dd/yyyy HH24:MI'));
INSERT INTO yourtable VALUES ( 2, TO_DATE ('3/8/2007 13:41', 'mm/dd/yyyy HH24:MI'));
INSERT INTO yourtable VALUES ( 3, TO_DATE ('3/8/2007 12:58', 'mm/dd/yyyy HH24:MI'));
-- row we want in our output:
INSERT INTO yourtable VALUES ( 4, TO_DATE ('3/8/2007 12:30', 'mm/dd/yyyy HH24:MI'));
INSERT INTO yourtable VALUES ( 5, TO_DATE ('3/8/2007 13:45', 'mm/dd/yyyy HH24:MI'));
INSERT INTO yourtable VALUES ( 6, TO_DATE ('3/8/2007 12:59', 'mm/dd/yyyy HH24:MI'));
-- row we want in our output:
INSERT INTO yourtable VALUES ( 7, TO_DATE ('3/8/2007 12:03', 'mm/dd/yyyy HH24:MI'));
INSERT INTO yourtable VALUES ( 8, TO_DATE ('3/8/2007 14:01', 'mm/dd/yyyy HH24:MI'));
INSERT INTO yourtable VALUES ( 9, TO_DATE ('3/8/2007 13:03', 'mm/dd/yyyy HH24:MI')); 
-- row we want in our output:
INSERT INTO yourtable VALUES (10, TO_DATE ('3/8/2007 12:23', 'mm/dd/yyyy HH24:MI'));

SELECT *
FROM   ( SELECT the_id
              , to_char(in_date,'HH24:MI') in_time
        FROM     yourtable
        ORDER BY in_date
       )
WHERE  ROWNUM <= 3
/

DROP TABLE yourtable PURGE
/

When I run it, I get this:
SQL> @orafaq

Table created.


1 row created.
...<snipped>...
1 row created.


    THE_ID IN_TI
---------- -----
         7 12:03
        10 12:23
         4 12:30


Table dropped.

MHE
Re: query for using rownum and order by inthe same query [message #262880 is a reply to message #262846] Tue, 28 August 2007 07:27 Go to previous message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Its working MHE!!! Smile
Lots of thanks !!!!
Regards
Sunil Gaurav
Previous Topic: HOw to apply priority on database triggers
Next Topic: SQL Server to Oracle
Goto Forum:
  


Current Time: Tue Dec 06 04:51:39 CST 2016

Total time taken to generate the page: 0.10349 seconds