Select 2 lowest values from a query in SQL

Question

I am trying get information of 2 lowest salary holders from default "scott" table.

This is the query I am trying:

SELECT TOP 2 * FROM emp ORDER BY sal ASC

But I'm getting this error:

ORA-00923: FROM keyword not found where expected

Screenshot:

Oracle does not have a `TOP` keyword to get the first n rows. — Joachim Isaksson, Feb 20 '16 at 20:35
@Md.ShohanHossain I know, but it doesn't stop the question being a duplicate and it is more valuable to other people to link to the other question with much more detailed answers. — MT0, Feb 20 '16 at 21:21
@MT0 don't worry. I am marking this question as duplicate. I didn't find the answer when I search for it. My bad. — Md. Shohan Hossain, Feb 20 '16 at 21:23

score 1 · Accepted Answer · answered Feb 20 '16 at 20:39

1

In the most recent versions of Oracle, you can use the ANSI standard:

SELECT emp.*
FROM emp
ORDER BY sal ASC
FETCH FIRST 2 ROWS ONLY;

In older versions:

SELECT e.*
FROM (SELECT emp.*
      FROM emp
      ORDER BY sal ASC
     ) e
WHERE rownum <= 2;

answered Feb 20 '16 at 20:39

Gordon Linoff

score 0 · Answer 2 · answered Feb 20 '16 at 20:38

0

You can use ROWNUM in Oracle to get two first rows of your query results.

SELECT EMP1.* FROM (SELECT * FROM EMP ORDER BY SAL ASC) EMP1 WHERE ROWNUM < 3;

answered Feb 20 '16 at 20:38

evilone

score 0 · Answer 3 · answered Feb 20 '16 at 20:39

0

You want

select * from emp ORDER BY sal ASC
fetch first 2 rows only

answered Feb 20 '16 at 20:39

Hogan

Thank you for these valuable information. :) – Md. Shohan Hossain Feb 20 '16 at 20:44
@Md.ShohanHossain - yw -- good luck – Hogan Feb 20 '16 at 20:45

3 Answers3