This gives me just one row (the first one):
SELECT BLA
FROM BLA
WHERE BLA
AND ROWNUM < 2
However, I want the most recent date val; I can make that the first row this way:
SELECT BLA
FROM BLA
WHERE BLA
ORDER BY FINALDATE DESC
When I try to mix the two partial victories, though, it doesn't work - apparently the "Select the first row only" logic fires before the order by, and then the order by is subsequently ignored.
This question is related to
sql
oracle
sql-order-by
In 12c, here's the new way:
select bla
from bla
where bla
order by finaldate desc
fetch first 1 rows only;
How nice is that!
You can nest your queries:
select * from (
select bla
from bla
where bla
order by finaldate desc
)
where rownum < 2
An alternative way:
SELECT ...
FROM bla
WHERE finalDate = (SELECT MAX(finalDate) FROM bla) AND
rownum = 1
Source: Stackoverflow.com