Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Subquery to return top row
Hi,
you did not mention your version of Oracle. For all i know beginning with 8.1.5 you can use order by in the subquery:
SQL*Plus: Release 8.1.6.0.0 - Production on Mo Jun 26 23:29:33 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Verbunden mit:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL> select rownum rank, ename, salary from (
2 select ename, sum(sal) salary
3 from scott.emp
4 group by ename
5 order by salary desc)
6 where rownum < 5;
RANK ENAME SALARY
---------- ---------- ----------
1 KING 5000 2 FORD 3000 3 SCOTT 3000 4 JONES 2975
SQL> hth
Klaus
<ahporter_at_montana.edu> schrieb im Newsbeitrag
news:8j8g47$urg$1_at_nnrp1.deja.com...
> I need to have a subquery return a particular row; I can create a normal
> query to get several rows and I can get the row I need to come to the
> top with ORDER BY. However, when I try to embed this query as a
> subquery, I get a "missing right parenthesis" error on the ORDER
> keyword.
>
> First, is it possible to use ORDER BY in a subquery?
>
> Second, I need to limit the results of the subquery to just the first
> row -- it is possible to somehow just select the top row? In other
> words, the row with rownum = 1?
>
> Here is the skeleton SQL that I currently have. What I need is the
> goremal_email_address that floats to the top in the subquery. The ?????
> marks are things that I do not know how to fill in. And I have no idea
> how to select just the first row that is returned. I have seen other
> examples that indicate this can be done; I just cannot figure out the
> syntax to accomplish it.
>
> Help! Thank you!
>
> SELECT
> SUBSTR(spriden_first_name,1,12) "FIRST"
> ,SUBSTR(spriden_last_name,1,12) "LAST"
> ,goremal_email_address "E-MAIL" <-- need one of these
> FROM
> spriden,goremal
> WHERE
> goremal_pidm = spriden_pidm
> AND ????? =
> (
> SELECT ?????
> FROM goremal
> WHERE goremal_pidm = spriden_pidm
> AND goremal_emal_code = 'EML'
> AND goremal_status_ind = 'A'
> AND goremal_preferred_ind = 'Y'
> ORDER BY goremal_preferred_ind DESC,
> goremal_activity_date DESC
> )
> ;
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Jun 26 2000 - 00:00:00 CDT