Selecting a single (random) row for an SQL join

Question

I've got an sql query that selects data from several tables, but I only want to match a single(randomly selected) row from another table.

Easier to show some code, I guess ;)

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

All ID-columns are primary keys, with appropriate FK constraints.

What I want, in english, is for eack row in K that has selected = 1 to get a random row from C where there exists a row in M with (K_id, C_id), where the score is higher than a given value, and where c.image is not null and there is a row in s with c_id

Something like:

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;

The only problem is this returns all the rows in C that match the criteria - I only want one...

I can see how to do it using PL/SQL to select all relevent rows into a collection and then select a random one, but I'm stuck as to how to select a random one.

Answer 1

I'm not too familiar with oracle SQL, but try using LIMIT random(), if there is such a function available.

Answer 2

Hi Visage,

with analytics:

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

This will select one row that satisfies your criteria per k_id. This will likely select the same set of rows if you run the query several times. If you want more randomness (each run produces a different set of rows), you would replace ORDER BY NULL by ORDER BY dbms_random.value

Answer 3

Hi,

you can use the 'order by dbms_random.random' instruction with your query.

i.e.:

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

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