CODEWITHSUNDEEP
sqldatabaseoracle-database
Trần Văn Thành
Trần Văn Thành

Reputation: 47

Select a random record with subquery in Oracle sql

When I try to get a random record into two tables from an Oracle database which have to a table is Order and order_detail. The result has to follow some condition that the order have 3 order_detail rows, and just retrieved a random order.

This is my code:

SELECT order_id
FROM Oder
INNER JOIN order_detail
ON orderId.order_id     = order_detail.FK_order_id
WHERE order_id IN
  (SELECT order_detail.FK_order_id
  FROM order_detail
  GROUP BY FK_order_id
  HAVING COUNT(FK_order_id) > = 3
  ) 
ORDER BY (SYS.DBMS_RANDOM.VALUE);

I DON'T KNOW WHERE TO PUT ROWNUM =1 TO GET ONLY ONE RECORD.

Upvotes: 1

Views: 1100

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175836

You need to wrap your query with subquery:

SELECT *
FROM (SELECT order_id
      FROM Oder
      JOIN order_detail
        ON orderId.order_id = order_detail.FK_order_id
      WHERE order_id IN (SELECT order_detail.FK_order_id
                         FROM order_detail
                         GROUP BY FK_order_id
                         HAVING COUNT(FK_order_id) > = 3) 
       ORDER BY (SYS.DBMS_RANDOM.VALUE)) s
WHERE rownum=1;

Starting from Oracle 12c you could use OFFSET-FETCH clause:

SELECT order_id
FROM Oder
INNER JOIN order_detail
ON orderId.order_id     = order_detail.FK_order_id
WHERE order_id IN
  (SELECT order_detail.FK_order_id
  FROM order_detail
  GROUP BY FK_order_id
  HAVING COUNT(FK_order_id) > = 3
  ) 
ORDER BY (SYS.DBMS_RANDOM.VALUE)
FETCH FIRST 1 ROW ONLY;

Upvotes: 1

user8406805
user8406805

Reputation:

You have to add ROWNUM after adding one more sub-query top on your SQL as, you are adding the RANDOM in order by and the ORDER BY is the last clause of SQL. hence you have one more level of SELECT as below.

select order_id from (SELECT order_id
FROM Oder
INNER JOIN order_detail
ON orderId.order_id     = order_detail.FK_order_id
WHERE order_id IN
  (SELECT order_detail.FK_order_id
  FROM order_detail
  GROUP BY FK_order_id
  HAVING COUNT(FK_order_id) > = 3
  ) 
ORDER BY (SYS.DBMS_RANDOM.VALUE)) where rownum <=1;

Upvotes: 1

Related Questions