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

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