Selecting one (random) row for SQL join

Question

I have a sql query that selects data from several tables, but I only want to match one (randomly selected) row from another table.

It’s easier to show some code, I think;)

Table K is (k_id, selected) Table C (c_id, image) Table S (c_id, date) Table M (c_id, k_id, rating)

All ID columns are primary keys with corresponding FK restrictions.

What I want in English is for an eck string in K that selected = 1 to get a random string from C, where there is a string in M ​​with (K_id, C_id), where the score is higher than the given value, and where c.image is not null and there is a line in s with c_id

Sort of:

select k.k_id, c.c_id, m.score
 from k,c,m,s
where k.selected = 1
  and m.score > some_value
  and m.k_id = k.k_id
  and m.c_id = c.c_id
  and c.image is not null
  and s.c_id = c.c_id;

, C, - ...

, , PL/SQL, , , , .

Answer 1

:

SELECT k_id, c_id, score
  FROM (SELECT k.k_id, c.c_id, m.score, 
               row_number() over(PARTITION BY k.k_id ORDER BY NULL) rk
           FROM k, c, m, s
          WHERE k.selected = 1
            AND m.score > some_value
            AND m.k_id = k.k_id
            AND m.c_id = c.c_id
            AND c.image IS NOT NULL
            AND s.c_id = c.c_id)
 WHERE rk = 1

, k_id. , , . ( ), ORDER BY NULL ORDER BY dbms_random.value

Answer 2

`order by dbms_random.random ' .

:.

SELECT column FROM
  (
    SELECT column FROM table
    ORDER BY dbms_random.value
  )
WHERE rownum = 1

: http://awads.net/wp/2005/08/09/order-by-no-order/ http://www.petefreitag.com/item/466.cfm

Answer 3

I am not very familiar with oracle SQL, but I will try to use LIMIT random () if there is such a function.