How do I do top 1 in Oracle?

Analytic prowess for top 1

For precise ordering, the sequence of operations is crucial. Be sure to order your data before filtering it with ROWNUM:

-- "First sort, then filter" - ancient SQL proverb.
SELECT *
FROM (
  SELECT * FROM your_table
  ORDER BY your_criteria DESC
)
WHERE ROWNUM = 1;


Top 1 within partitions

If you're after the best entry within each group, ROW_NUMBER() combined with PARTITION BY will do the trick:

-- Like a SQL Olympics, every category gets its champion.
SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY criteria) as rn
  FROM your_table
)
WHERE rn = 1;


This code assigns each group its own top entry.

The Oracle 12c leap

Upon stepping into Oracle 12c, top row selection eases up with OFFSET/FETCH NEXT:

-- SQL said: OFFSET your burdens, FETCH your dreams.
SELECT *
FROM your_table
ORDER BY your_criteria
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;


Keep in mind, this efficient restriction supports only Oracle 12c onwards.

The analytical methods

Beyond ROWNUM, Oracle sports a suite of analytic functions for ranked data retrieval:

Exacting row_number()

For enhanced sorting, ROW_NUMBER() is a sharp tool:

-- row_number() - SQL's own Sorting Hat without the sorting hat.
SELECT fname, ROW_NUMBER() OVER (ORDER BY some_factor) rank
FROM MyTbl
WHERE rank = 1;


Maximize using max()

Need the maximum value coupled with row ordering? Maximize it:

-- Who's the max now?
SELECT fname, MAX(fname) OVER (ORDER BY some_factor)
FROM MyTbl;


Harmonizing with Oracle 11g

While later versions provide paging features, in Oracle 11g, you'll need either handy subqueries or fitting analytic functions.

Further slicing and dicing

Being content with just the top row? Don't limit yourself! Let's dissect a dataset for top N entries or even by partition subsets.

Analytical ranking for top N

To snatch your top N records, fall back on RANK() or DENSE_RANK():

-- Because sometimes, we may want a couple of medalists.
SELECT fname,
       RANK() OVER (ORDER BY some_factor) rank
FROM MyTbl
WHERE rank <= N;


Mind the difference: RANK() leaves gaps after ties, DENSE_RANK() does not.

Subsetting top N

Retrieving a top N for each category? Add some PARTITION BY magic:

-- And the nominees in each category are...
SELECT fname, ROW_NUMBER() OVER (PARTITION BY category ORDER BY some_factor) rn
FROM MyTbl
WHERE rn <= N;


Each category receives a separate top N set. It's like having mini Olympics within the big one.