Home » SQL & PL/SQL » SQL & PL/SQL » Simple one - top n records
Simple one - top n records [message #18384] Wed, 30 January 2002 01:30 Go to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Hi, thanks in advance - I'm trying to select the top 20 records returned from the following query, but when I add the rownum < 21 at the end I keep getting syntax errors. Obviously I'm missing something simple. Have tried various permutation but no joy.

SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
FROM
(SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
FROM TOP20 where Customer='Canada'
and Priority='Minor') ORDER BY TotalActiveDays desc;
Re: Simple one - top n records [message #18386 is a reply to message #18384] Wed, 30 January 2002 02:11 Go to previous messageGo to next message
Ganduri Umamaheswar
Messages: 1
Registered: January 2002
Junior Member
hai,
Try the following
SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
FROM
(SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
FROM TOP20 where Customer='Canada'
and Priority='Minor' ORDER BY TotalActiveDays desc)where rownum<21;

Ganduri Umamaheswar
Re: Simple one - top n records [message #18389 is a reply to message #18384] Wed, 30 January 2002 03:36 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Thanks Mike but is that Rank function for Oracle 8i and greater ? The Oracle on the machine I'm using is only 7.3
Re: Simple one - top n records [message #18395 is a reply to message #18389] Wed, 30 January 2002 04:58 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
Sorry I forgott to mention it, yes it is only for Oracle 8i and higher
Re: Simple one - top n records [message #18399 is a reply to message #18384] Wed, 30 January 2002 05:35 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Ganduri's solution worked for me. Can you copy and paste the query and the error and post it?
Re: Simple one - top n records [message #18401 is a reply to message #18384] Wed, 30 January 2002 05:42 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
The query is exactly the same as Ganduri's - sorry about the messy format.
Cheers Paul

SQLWKS> SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
2> FROM
3> (SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
4> FROM TOP20 where Customer='Canada Life'
5> and Priority='Minor' ORDER BY TotalActiveDays desc)where rownum<21;

ERROR MSG
ORDER BY TotalActiveDays desc)where rownum<21
*
ORA-00907: missing right parenthesis
SQLWKS>
Re: Not so simple one, I guess - top n records [message #18402 is a reply to message #18384] Wed, 30 January 2002 06:03 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Strange...works for me. Try running just the select from within the parentheses. See if that works.

If yes, try "select * from (select * from top20 order by totalActiveDays desc)" Also, "select * from (select * from top20)"
Simple in the end - top n records [message #18407 is a reply to message #18384] Wed, 30 January 2002 07:04 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Got it - worked fine when I put the Rownum before the Order by.
Thanks for the help

SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
FROM
(SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
FROM TOP20 where Customer='Canada'
and Priority='Minor') where rownum<4 ORDER BY TotalActiveDays desc;
Re: Simple in the end - top n records - not really!! [message #18410 is a reply to message #18407] Wed, 30 January 2002 07:30 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Sorry to burst your euphoria, Paul, but this query will not necessarily give you the results you are looking for - the order by has to be in the inline query. Otherwise:

12:22:01 ==> select * from top20;

CUSTOMER TOTALACTIVEDAYS HEADING PRIORITY DATECOMPOS
-------------------- --------------- -------------------- -------------------- ----------
Canada 10 Minor
Canada 11 Minor
Canada 12 Minor
Canada 13 Minor
Canada 14 Minor
Canada 15 Minor
Canada 16 Minor
Canada 17 Minor
Canada 18 Minor
Canada 19 Minor
Canada 101 Minor
Canada 102 Minor
Canada 103 Minor
Canada 104 Minor
Canada 105 Minor
Canada 106 Minor
Canada 107 Minor
Canada 108 Minor
Canada 109 Minor
Canada 110 Minor
Canada 120 Minor

CUSTOMER TOTALACTIVEDAYS HEADING PRIORITY DATECOMPOS
-------------------- --------------- -------------------- -------------------- ----------
Canada 130 Minor
Canada 140 Minor
Canada 150 Minor
Canada 160 Minor
Canada 170 Minor
USA 145 Minor
USA 30 Minor

28 rows selected.

12:22:31 ==> SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
12:22:35 2 FROM
12:22:35 3 (SELECT Customer, Heading, Priority, DateComposed, TotalActiveDays
12:22:35 4 FROM TOP20 where Customer='Canada'
12:22:35 5 and Priority='Minor') where rownum<21 ORDER BY TotalActiveDays desc
12:22:37 6 /

CUSTOMER HEADING PRIORITY DATECOMPOS TOTALACTIVEDAYS
-------------------- -------------------- -------------------- ---------- ---------------
Canada Minor 110
Canada Minor 109
Canada Minor 108
Canada Minor 107
Canada Minor 106
Canada Minor 105
Canada Minor 104
Canada Minor 103
Canada Minor 102
Canada Minor 101
Canada Minor 19
Canada Minor 18
Canada Minor 17
Canada Minor 16
Canada Minor 15
Canada Minor 14
Canada Minor 13
Canada Minor 12
Canada Minor 11
Canada Minor 10

20 rows selected.
Ordering with rownum has to be written like [message #18411 is a reply to message #18407] Wed, 30 January 2002 07:53 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
Ordering with rownum has to be written like:
SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;

HTH
Mike
Re: Ordering with rownum has to be written like [message #18414 is a reply to message #18407] Wed, 30 January 2002 07:59 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Yes, that's what I was trying to say, Mike.
Re: Ordering with rownum has to be written like [message #18441 is a reply to message #18407] Thu, 31 January 2002 03:13 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Yeh, discover that when I checked the figures - back to the missing right parenthesis muck.

Thanks anyway lads
Re: Ordering with rownum has to be written like - Workaround [message #18444 is a reply to message #18407] Thu, 31 January 2002 04:13 Go to previous message
Paul
Messages: 164
Registered: April 1999
Senior Member
Due to the funny missing parenthesis error - I used the following workaround - I changed the view the query is based on to include a group by (as you can't use order by)
create view TOP20
SELECT Customer, Heading, Priority, Status, DateComposed, SysDate-Datecomposed as TotActiveDays
FROM sr where status!='Closed' and Status!='Solution Built' group by
DateComposed, SysDate-Datecomposed, Customer, Heading, Priority, Status, rownum

and then changed the query to -
myTOP20="SELECT Customer, Heading, Priority, Status, DateComposed, TotActiveDays FROM " & _
"(SELECT Customer, Heading, Priority, Status, DateComposed, TotActiveDays " & _
"FROM TOP20) where rownum < 21

This appears to work fine
Previous Topic: Pack a PL/SQL Table
Next Topic: MIKE YOU ARE RIGHT! NOW, HOW DO I GET AROUND THIS PROBLEM?
Goto Forum:
  


Current Time: Fri Mar 29 02:20:04 CDT 2024