Simple one - top n records [message #18384] |
Wed, 30 January 2002 01:30 |
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 |
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 #18401 is a reply to message #18384] |
Wed, 30 January 2002 05:42 |
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 |
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 |
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 |
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.
|
|
|
|
|
|
Re: Ordering with rownum has to be written like - Workaround [message #18444 is a reply to message #18407] |
Thu, 31 January 2002 04:13 |
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
|
|
|