How to select top five or 'N' rows in Oracle 11g

Question

select distinct ani_digit, ani_business_line from cta_tq_matrix_exp limit 5

I want to select top five rows from my resultset. if I used above query, getting syntax error.

actually. i don't want to order my resultset. just I want 5 rows alone. — Karthikeyan Sukkoor, Dec 18 '13 at 11:16
@KarthikeyanSukkoor - So you don't care which 5 rows you get? — Justin Cave, Dec 18 '13 at 11:19
yes. I am taking distinct of two column and any five rows are enough — Karthikeyan Sukkoor, Dec 18 '13 at 11:21

score 34 · Accepted Answer · answered Dec 18 '13 at 14:03

34

You'll need to use DISTINCT before you select the "top 5":

SELECT * FROM 
(SELECT DISTINCT ani_digit, ani_business_line FROM cta_tq_matrix_exp) A
WHERE rownum <= 5

answered Dec 18 '13 at 14:03

D Stanley

149,601
11
178
240

Thanks.. It s working – Karthikeyan Sukkoor Dec 18 '13 at 14:08

score 3 · Answer 2 · answered Dec 18 '13 at 17:13

3

  select distinct ani_digit, ani_business_line from cta_tq_matrix_exp where rownum<=5;

answered Dec 18 '13 at 17:13

Sai

659
4
12
21

it is not working to my case. – Karthikeyan Sukkoor Dec 19 '13 at 07:04
this is a common mistake, where oracle 11 g, will pick N as per rownum randomly, and then do order. starting by oracle 12 c limit key word available. https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results – Mohamed.Abdo Jul 18 '18 at 13:24

score 2 · Answer 3 · answered Dec 18 '13 at 11:19

2

LIMIT clause is not available in Oracle.

Seeing your query, you seem to be interested only in a certain number of rows (not ordered based on certain column value) and so you can use ROWNUM clause to limit the number of rows being returned.

select distinct ani_digit, ani_business_line from cta_tq_matrix_exp WHERE rownum <= 5

If you want to order the resultset and then limit the number of rows, you can modify your query as per the details in the link provided by Colin, in the comments above.

answered Dec 18 '13 at 11:19

Incognito

2,964
2
27
40

yes. I did this query. but getting only one row in the resultset. if I used query like "select distinct ani_digit, ani_business_line from cta_tq_matrix_exp" getting three rows as result. i am feel some issue with distinct key. – Karthikeyan Sukkoor Dec 18 '13 at 11:24
Just.. use any one of you local table and take the distinct value of two columns and find the top five rows. – Karthikeyan Sukkoor Dec 18 '13 at 11:32
If you're getting 3 rows with `DISTINCT`, then `ROWNUM` cannot return just one row, or reduce the number of rows. – Incognito Dec 18 '13 at 11:32
no. i am getting like that only. kindly try one example from your local database. – Karthikeyan Sukkoor Dec 18 '13 at 11:33
Check this, and let me know if it fits your case http://www.sqlfiddle.com/#!4/e16ba/1/0 – Incognito Dec 18 '13 at 11:36
your explaination is fine. But I don't know that why am i not getting this result from my table – Karthikeyan Sukkoor Dec 18 '13 at 13:50
1

**Equivalent of `LIMIT` clause _is_ available in Oracle** starting from 12cR1: http://stackoverflow.com/a/26051830/1461424 – sampathsris Sep 26 '14 at 10:37
Does this call still cause a full table scan? Or will the query stop after just pulling the first 5 rows? (We ask because we have been reading that row numbers are assigned *after* the query is run - and we are wondering if there is any way to decrease the processor load.) – Praxiteles Dec 16 '18 at 04:27

How to select top five or 'N' rows in Oracle 11g

3 Answers3