CODEWITHSUNDEEP
sqloracle-database
Robben
Robben

Reputation: 463

SQL query on a random row via Oracle

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

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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.

Upvotes: 3

Related Questions