RE: SQL Q: Ten highest rows

From: Jack Silvey <JSilvey_at_XOL.com>
Date: Mon, 19 Jun 2000 16:03:00 -0500
Message-Id: <10533.109782@fatcity.com>

To return exactly "n" rows ordered by a column, create a query similar to the following:  

SELECT *
FROM table A
WHERE n >= (SELECT COUNT(*)

	    FROM table B 
            WHERE B.column >= A.column)  

ORDER BY column DESC;  

where "n"      is the number of rows to be retrieved and ordered,  
      "table"  is the name of the table   
and   "column" is the name of the column. 
 

The first SELECT statement controls the number of rows, while the nested, second SELECT statement controls the condition.  

The query returns the first "n" ordered rows. If n=10 and Rows 8-12 share the
same column value, this only return Rows 9 and 10. The above SELECT statement
does not take duplicate column values into consideration.    

Solution Explanation:

---

 

To return any row whose column value is one of the top "n" ordered column values, create a query similar to the following:  

SELECT *
FROM table A
WHERE n >= (SELECT COUNT(DISTINCT column)

            FROM column B  
            WHERE B.column >= A.column) 

ORDER BY column DESC;  

Specifying DISTINCT return all rows whose column (e.g. sal) value falls into

one of the top "n" values. Hence, the query returns more than "n" rows when

several rows share the same column value.

Or, just upgrade to 8i, which allows "order by" in subqueries.

-----Original Message-----
From: Djordje Jankovic [mailto:djankovic_at_corp.attcanada.ca] Sent: Monday, June 19, 2000 2:11 PM
To: Multiple recipients of list ORACLE-L Subject: SQL Q: Ten highest rows

Friends,

A SQL question (not PL/SQL - I know how to do it in PL/SQL, but it is pretty clumsy).

Can you select e.g. ten rows with the highest value of a certain column ?

It is not allowed to put "order by" in a subquery, e.g. :

select val
  from (select val from tab order by val)  where rownum < 11),

and rownum is applied before the group by, (e.g.:

select val
  from tab
 where rownum < 11
order by val

Thanks a lot.

Djordje

-- 
Author: Djordje Jankovic
  INET: djankovic_at_corp.attcanada.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Mon Jun 19 2000 - 16:03:00 CDT