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

Question

How to limit the output to only get the first row in ORACLE. I tried FETCH first 1 rows only; But it is giving sql not ended properly error message

Current OUTPUT

 70 19-APR-18   Base Line Date
 71 20-JUN-19   Target Date
 73 23-JUN-18

QUERY

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

Required Output

70  19-APR-18   Base Line Date

score 2 · Answer 1 · edited Oct 17 '18 at 05:00

2

You need

WHERE ROWNUM=1

Oracle doesn't have a top X function, but it always knows what the rownumber, so take advantage.

edited Oct 17 '18 at 05:00

Barbaros Özhan

59,113
10
31
55

answered Oct 16 '18 at 16:02

yoelbenyossef

393
1
7
26

I directly tried adding this to the query, but it didnt work. It gives me wrong result – Neha Oct 16 '18 at 16:08
Have you taken your order by out of the inner query and put it into the outer query? – yoelbenyossef Oct 16 '18 at 16:11

Barbaros Özhan · Accepted Answer · 2018-10-16T17:27:31.813

2

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.

edited Oct 16 '18 at 17:27

answered Oct 16 '18 at 16:03

Barbaros Özhan

59,113
10
31
55

So there is no way to add rownum = 1 where clause in the same query , i will have to make the selection once again? – Neha Oct 16 '18 at 16:09
Your first rownum version was right; your modified version will get a random/indeterminate row, as without the subquery the order-by is applied *after* it's filtered to a single row - which will be the first one it sees, based on however the optimiser executes the rest of the query. (In the row-number one you don't really need the order-by clause, but what is the 'day' in the `over` clause? Should that be `tracker_session_seq`?) – Alex Poole Oct 16 '18 at 16:48
I had already tried the first option Barbaros before adding the query in stackoverflow, but its not giving me correct values, never mind i will go with the second option only. – Neha Oct 16 '18 at 16:50
1

@AlexPoole sure thanks you're right, the keywords `random/indeterminate` are important about this approach. – Barbaros Özhan Oct 16 '18 at 17:28

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

2 Answers2