| top-N query [message #375129] |
Mon, 30 July 2001 02:24  |
Norvin
Messages: 22
Registered: July 2001
|
Junior Member |
|
|
I want to make a query and i need the top 50 row. But when i issue the following command i got an error message:
SELECT * FROM
(SELECT * FROM table1 ORDER BY field1)
WHERE rownum < 51;
ORA-00907: missing right parenthesis
(SELECT * FROM table1 ORDER BY field1)
*
was it that, i cannot make an ORDER BY statement in the FROM clause? If yes how can retrieve that top 50 row?
Our Database version is :
Oracle7 Server Release 7.3.4.0.0 - Production
PL/SQL Release 2.3.4.0.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for Intel SVR4 UNIX: Version 2.3.4.0.0 - Production
NLSRTL Version 3.2.4.0.0 - Production
|
|
|
|
|
|
| Re: top-N query [message #375132 is a reply to message #375129] |
Mon, 30 July 2001 07:57  |
Hans
Messages: 42
Registered: September 2000
|
Member |
|
|
because group by does implicit a sorting operation you can use sometimes as a workaround
the <b>group by clause</b> with Oracle 7.3 - Oracle 8.0.5
select field1, field2, field3, .. , fieldn from
(
select field1, field2, field3, .. , fieldn, rowid from table
group by field1, field2, field3, .. , fieldn, rowid
)
where rownum < 51;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
