CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
Random guy
Random guy

Reputation: 923

Second method for getting output using SQL query in ORACLE

I have a table which has data as:

enter image description here

My expected output is:

enter image description here

I got my expected output as using rownum:

SELECT ID,PRICE FROM OT.TEST1 WHERE ROWNUM<3;

It's working finesince i have inserted the data serially as the output is coming with rownum ,but what,if the data were inserted as random below,my rownum will not work.Is there any new method?

ID PRice
3  300
3  600
8  600
2  600

Upvotes: 0

Views: 28

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521028

You could use ROW_NUMBER() here:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY PRICE, ID) rn
    FROM OT.TEST1 t
)

SELECT ID, PRICE
FROM cte
WHERE rn <= 2;

Here we are assigning a row number over the entire table ordered first by price ascending, and then by ID. Since three records are tied for a price of 600, the record with the lowest ID would happen to be returned here.

Upvotes: 1

Related Questions