How to Select Only 1 Row from Oracle SQL

How to limit the results to 1 row in Oracle SQL

In Oracle you need to do the ordering first and then select rownum. Thus, you need to nest the query which returns the sorted data and take the filtering WHERE clause outside.

SELECT * FROM
(
 SELECT customerNumber
 FROM ORDERS 
 GROUP BY customerNumber 
 ORDER BY count(orderNumber) DESC
) resultSet
WHERE ROWNUM=1;

How do I do top 1 in Oracle?

If you want just a first selected row, you can:

select fname from MyTbl where rownum = 1

You can also use analytic functions to order and take the top x:

select max(fname) over (rank() order by some_factor) from MyTbl

Case statement in Oracle for selecting single row

You might try something like the following:

SELECT wuser_id, wcompleted, wstatusdate FROM (
    SELECT wuser_id, wcompleted, wstatusdate, ROW_NUMBER() OVER ( PARTITION BY wuser_id ORDER BY wcompleted DESC, wstatusdate DESC ) AS rn
      FROM tblA
) WHERE rn = 1;

or you could do the following:

SELECT wuser_id, MAX(wcompleted) AS wcompleted
     , MAX(wstatusdate) KEEP (DENSE_RANK FIRST ORDER BY wcompleted DESC) AS wstatusdate
  FROM tblA
 GROUP BY wuser_id;

Get first row in Oracle Sql

I think the best way to get this is to use a subquery as the rownum is being looked at before the order by so:

select * from (
SELECT DISTINCT name, age
 FROM donates, persons
 WHERE name = donor 
 AND name IN (SELECT receiver FROM donates)
 ORDER BY age DESC
) where ROWNUM <= 1;

For a longer read look at http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

---

Related Topics