Select a random record with subquery in Oracle sql

Question

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.

Is this correct SQL statement? Seems it should be 'ON oder.order_id = order_detail.FK_order_id' or 'ON order.order_id = order_detail.FK_order_id' if table name spelled incorrectly — oreh
– oreh, Commented May 6, 2018 at 5:28

user8406805 · Accepted Answer · 2018-05-06 05:09:17Z

1

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;

answered May 6, 2018 at 5:09

user8406805

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

Comments

Lukasz Szozda · Accepted Answer · 2018-05-06 05:19:09Z

1

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;

edited May 6, 2018 at 5:19

answered May 6, 2018 at 5:08

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

1 Comment

Trần Văn Thành Over a year ago

Could you explain for me why I should be use subquery than join statement.

Collectives™ on Stack Overflow

Select a random record with subquery in Oracle sql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related