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

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.

Answer 1

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

Answer 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.

Answer 3

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