SQL query on a random row via Oracle

Question

I am using ORACLE database and I am trying to access one random row from my SQL query however the query is not retrieving back a random row and I am not sure what I did wrong?

My query is:

SELECT a.car_id, b.product_id
FROM listing a, carProduct b
WHERE a.car_id = b.car_id
AND a.certified = 'TRUE'
AND b.product_id like '%CERT'
AND rownum = 1
ORDER BY DBMS_RANDOM.RANDOM

what do you mean by not working correctly?

Vamsi Prabhala
– Vamsi Prabhala

2016-09-30 19:05:45 +00:00
Commented Sep 30, 2016 at 19:05 — Vamsi Prabhala
– Vamsi Prabhala, Commented Sep 30, 2016 at 19:05
@vkp I have edit my question.

Robben
– Robben

2016-09-30 19:06:33 +00:00
Commented Sep 30, 2016 at 19:06 — Robben
– Robben, Commented Sep 30, 2016 at 19:06

Gordon Linoff · Accepted Answer · 2016-09-30 19:08:42Z

3

The rownum has to come after the order by. You need a subquery:

SELECT lcp.*
FROM (SELECT l.car_id, cp.product_id
      FROM listing l join
           carProduct cp
           on l.car_id = cp.car_id
      WHERE l.certified = 'TRUE' AND cp.product_id like '%CERT'
      ORDER BY DBMS_RANDOM.RANDOM
     ) lcp
WHERE rownum = 1;

Notes:

Learn to use proper, explicit JOIN syntax. Simple rule: Never use commas in the FROM clause.
Use table aliases that make sense, such as abbreviations for the table names.
The rownum = 1 needs to go in the outer query.
In Oracle 12c+, you don't need a subquery. You can just use fetch first 1 row only.

answered Sep 30, 2016 at 19:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Robben Over a year ago

Thanks a bunch, this is very helpful! One question, what does lcp.* do? I tried googling it but I couldn't find anything.

Gordon Linoff Over a year ago

@Robben . . . It selects all the columns from the subquery in the from clause that is labelled lcp.

Collectives™ on Stack Overflow

SQL query on a random row via Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related