Second method for getting output using SQL query in ORACLE

Question

I have a table which has data as:

My expected output is:

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?

The query you posted is only guaranteed to return an arbitrary 2 rows-- any 2 rows would be equally valid. If you want to return a specific 2 rows, you'd need to tell us which rows you want to return. I have no idea how you determined that you wanted (3,300) and (2,600) to be returned rather than either of the other two rows. — Justin Cave
– Justin Cave, Commented Sep 30, 2019 at 15:31
Table data is considered unordered. It is left to chance which two rows you get with WHERE ROWNUM < 3. — Thorsten Kettner
– Thorsten Kettner, Commented Sep 30, 2019 at 15:34

Tim Biegeleisen · Accepted Answer · 2019-09-30 15:32:50Z

1

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.

answered Sep 30, 2019 at 15:32

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Second method for getting output using SQL query in ORACLE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related