How to Do Top 1 in Oracle

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

How to select top 1 and ordered by date in Oracle SQL?

... where rownum = 1 order by trans_date desc

This selects one record arbitrarily chosen (where rownum = 1) and then sorts this one record (order by trans_date desc).

As shown by Ivan you can use a subquery where you order the records and then keep the first record with where rownum = 1in the outer query. This, however, is extremely Oracle-specific and violates the SQL standard where a subquery result is considered unordered (i.e. the order by clause can be ignored by the DBMS).

So better go with the standard solution. As of Oracle 12c:

select * 
from table_name 
order by trans_date desc
fetch first 1 row only;

In older versions:

select *
from
(
  select t.*, row_number() over (order by trans_date desc) as rn
  from table_name t
)
where rn = 1;

Equivalent of SQL Server's TOP 1 in Oracle (without using rownum or row_number())

Oracle does not support TOP 1, as you pointed out. You might be able to rewrite in Oracle while maintaining the correlated subquery, but the best option would probably be to remove that subquery, and instead just use the join you already making to handle the logic:

WITH cte AS (
    SELECT
        PA.*,
        COALESCE(CI.DISCOUNTCODE, 'NA') AS DISCOUNTCODE,
        ROW_NUMBER() OVER (PARTITION BY CI.ID ORDER BY CI.DATE DESC) rn
    FROM PAYMENT PA
    LEFT JOIN CONTRACTINFO CI
        ON PA.CONTRACTID = CI.ID AND
           CI.DATE < PA.PAYMENTDATE 
    WHERE
        CI.BASERECORD = 1
)

SELECT CONTRACTID, TIME, PAYMENTDATE, DISCOUNTCODE
FROM cte
WHERE rn = 1;

Oracle SELECT TOP 10 records

You'll need to put your current query in subquery as below :

SELECT * FROM (
  SELECT DISTINCT 
  APP_ID, 
  NAME, 
  STORAGE_GB, 
  HISTORY_CREATED, 
  TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE  
  FROM HISTORY WHERE 
    STORAGE_GB IS NOT NULL AND 
      APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009')
  ORDER BY STORAGE_GB DESC )
WHERE ROWNUM <= 10

Oracle applies rownum to the result after it has been returned.
You need to filter the result after it has been returned, so a subquery is required. You can also use RANK() function to get Top-N results.



For performance try using NOT EXISTS in place of NOT IN. See this for more.

How to find the Limit the row to top 1 in oracle

Use rownum=1 as :

select * from
( 
SELECT  EXEC_TRACKER_SESSION_SEQ,
(CASE WHEN tsm.SESSION_SEQ IS NULL THEN tsm.TAGETDATE ELSE s.SESSION_DATE END) as SESSION_DATE
,tsm.NOTES 
FROM TRACKER_SESSION_MAP tsm
LEFT JOIN session s ON tsm.session_seq = s.session_seq
WHERE tsm.TRACKER_SEQ =244
order by TRACKER_SESSION_SEQ ASC
) 
where rownum=1;

Alternatively you may use row_number() function as :

select * from
(
 SELECT  EXEC_TRACKER_SESSION_SEQ,
(CASE WHEN tsm.SESSION_SEQ IS NULL THEN tsm.TAGETDATE ELSE s.SESSION_DATE END) as SESSION_DATE
,tsm.NOTES, row_number() over (order by day) as rn 
FROM TRACKER_SESSION_MAP tsm
LEFT JOIN session s ON tsm.session_seq = s.session_seq
WHERE tsm.TRACKER_SEQ =244
order by TRACKER_SESSION_SEQ ASC
) 
where rn=1;

fetch..rows statement works provided that you use Oracle 12c.

How do I limit the number of rows returned by an Oracle query after ordering?

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update:
To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2:
Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

How to Select Top 100 rows in Oracle?

Assuming that create_time contains the time the order was created, and you want the 100 clients with the latest orders, you can:

• add the create_time in your innermost query
• order the results of your outer query by the create_time desc
• add an outermost query that filters the first 100 rows using ROWNUM

Query:

  SELECT * FROM (
     SELECT * FROM (
        SELECT 
          id, 
          client_id, 
          create_time,
          ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn 
        FROM order
      ) 
      WHERE rn=1
      ORDER BY create_time desc
  ) WHERE rownum <= 100

UPDATE for Oracle 12c

With release 12.1, Oracle introduced "real" Top-N queries. Using the new FETCH FIRST... syntax, you can also use:

  SELECT * FROM (
    SELECT 
      id, 
      client_id, 
      create_time,
      ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn 
    FROM order
  ) 
  WHERE rn = 1
  ORDER BY create_time desc
  FETCH FIRST 100 ROWS ONLY)

Oracle Join Condition with Top 1

I'm still not clear which record, in your example, you wanted, so let me give you some alternatives.

I gathered from your problem statement that you want the latest effective date of those available. Since there are ties involved, you cannot use max() (as you've already stated), and row_number() is the way to go:

with cte as (
  select
    addrid, effectdt, xpirdt,
    row_number() over (partition by addrid order by effectdt desc) as rn
  from addrdata
)
select
  addrid, effectdt, xpirdt
from cte
where rn = 1

The next part was where you lost me with the nulls... If your secondary sort is by expiration date and you want a null value to trump the latest expiration date, then you would order by the expiration date and put nulls first:

with cte as (
  select
    addrid, effectdt, xpirdt,
    row_number() over 
        (partition by addrid order by effectdt desc, xpirdt nulls first) as rn
  from addrdata
)
select
  addrid, effectdt, xpirdt
from cte
where rn = 1

Which means this row is the winner:

10948448    5/14/2015   <null>

If, however, you wanted nulls to be considered only if there are no expiration dates, you can use nulls last (or omit it, as it is the default):

with cte as (
  select
    addrid, effectdt, xpirdt,
    row_number() over
        (partition by addrid order by effectdt desc, xpirdt nulls last) as rn
  from addrdata
)
select
  addrid, effectdt, xpirdt
from cte
where rn = 1

Meaning this guy has won the prize:

10948448    5/14/2015   5/13/2015

Since this uses row_number() you will not lose any rows -- each row is guaranteed to have a row number. It's just that if there are true ties, then it's a toss-up as to which row is chosen. However, your issue with null expiration dates should not cause any problems with this approach.

-- edit 2/13/16 --

I think I'm starting to understand your issue, but I'm not 100% sure. I've incorporated snippets of your code with the left join with my suggestion, and the need to have null expiration dates first, and this is my next crack:

with cte as (
  select
    addrid, effectdt, xpirdt,
    row_number() over 
        (partition by addrid order by effectdt desc, xpirdt nulls first) as rn
  from addrdata
)
select
  cte.addrid, effectdt, xpirdt
from
  mbr_person mb
  left join partyxref px on
    mb.nameid = px.nameid and
    px.reftype = 'COMM'
  left join cte on
    px.refkey = cte.addrid and
    cte.rn = 1

Assuming this doesn't do it:

1. When you say a null XPIRDT takes precedence -- do you mean even over more recent effective dates, or is it only a tiebreaker for the most recent EFFECTDT? If the latter, then what I have should work. If the former, then we need to switch the order by's in the analytic function
2. I am totally guessing when it comes to the partyxref and mbr_person tables. If this doesn't cut it, maybe post some sample data and desired output for including those two tables, or fiddle it?

---

Related Topics