How to Select Top 1 and Ordered by Date in Oracle SQL

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;

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

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.

SQL How to Select the most recent date item

Select * 
FROM test_table 
WHERE user_id = value 
AND date_added = (select max(date_added) 
   from test_table 
   where user_id = value)

Selecting the first N rows of each group ordered by date

As well as the row_number solution, another option is CROSS APPLY(SELECT TOP:

SELECT m.masterid,
       d.detailid,
       m.numbers,
       d.date_time,
       d.value
    FROM masters AS m
    CROSS APPLY (
        SELECT TOP (3) *
        FROM details AS d
        WHERE d.date_time >= '2020-01-01'
        AND m.masterid = d.masterid
    ) AS d
    WHERE m.tags LIKE '%Tag2%'
    ORDER BY m.masterid DESC,
             d.date_time;

This may be faster or slower than row_number, mostly depending on cardinalities (quantity of rows) and indexing.

If indexing is good and it's a small number of rows it will usually be faster. If the inner table needs sorting or you are anyway selecting most rows then use row_number.

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;

select max date in oracle

Try this:

SELECT order_id,
  status,
  update_date
FROM(SELECT order_id,
            status,
            update_date,
            ROW_NUMBER() over (partition BY order_id order by DECODE(status,'success',1,0) DESC,update_date DESC) rn
      FROM test1)
WHERE rn=1;

P.S : test1 is my table name with your data.

sql select everything with maximum date (that that is smaller than a specific date) without subqueries

You can find the maximum date without sub-queries.

SELECT t.*,
       max("DATE") OVER(PARTITION BY id ORDER BY "DATE" DESC) as max_date
FROM   "TABLE" t
WHERE  "DATE" < DATE '2021-07-16'

You need a sub-query to filter to only show the row(s) with the maximum date:

SELECT *
FROM   (
  SELECT t.*,
         max("DATE") OVER(PARTITION BY id ORDER BY "DATE" DESC) as max_date
  FROM   "TABLE" t
  WHERE  "DATE" < DATE '2021-07-16'
)
WHERE  "DATE" = max_date;

However, you are still only querying the table once using this technique even though it uses a sub-query.

Note DATE and TABLE are reserved words and cannot be used as unquoted identifiers; it would be better practice to use different names for those identifiers.

You could, equivalently use the RANK or DENSE_RANK analytic functions instead of MAX; ROW_NUMBER, however, does not give the same output as it will only return a single row and will not return all tied rows.

SELECT *
FROM   (
  SELECT t.*,
         RANK() OVER(PARTITION BY id ORDER BY "DATE" DESC) as rnk
  FROM   "TABLE" t
  WHERE  "DATE" < DATE '2021-07-16'
)
WHERE  rnk = 1;

But you still need a sub-query to filter the rows.

---

If you want to not use a sub-query then you can use:

SELECT id,
       MAX("DATE") AS "DATE",
       MAX(col1) KEEP (DENSE_RANK LAST ORDER BY "DATE", ROWNUM) AS col1,
       MAX(col2) KEEP (DENSE_RANK LAST ORDER BY "DATE", ROWNUM) AS col2,
       MAX(col3) KEEP (DENSE_RANK LAST ORDER BY "DATE", ROWNUM) AS col3
FROM   "TABLE"
GROUP BY id

However, that is not quite the same as it will only get a single row per id and will not return multiple rows tied for the greatest date per id.

---

Related Topics