SQLite Random sub query result-CodePudding

I have 2 SQLite tables, table_a and table_b. Table A contains a list of categories and Table B contains a list of words.

I'm trying to get all categories from table_a and a random word from table_b for each category. The below query allows me to get 1 result per category that has the lowest id in table_b.

    SELECT table_a.category_id, table_a.category, subQuery.word, subQuery.word_category, subQuery.word_id
    FROM table_a,
    (SELECT *
      FROM table_b
      GROUP BY category_id
      HAVING MIN(word_id)
    ) subQuery
WHERE subQuery.category_id = table_a.category_id;

Is there a way to return 1 random result from table_b for each category in table_a instead of the value with the lowest id?

I'm not really having much luck finding an answer online. Any help would be appreciated.

CodePudding user response：

You can use a window function to pick one row per category:

select
  c.category_id, c.category,
  w.word, w.word_category, w.word_id
from categories c
left join
(
  select
    word, word_category, word_id, category_id,
    row_number() over (partition by category_id order by random()) as rn
  from words
) w on w.category_id = c.category_id and w.rn = 1
order by c.category_id;

Demo: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=bbbe8541adf2d4f62883b2a1c36aaa41

Update

You say that the query does not work in SQLite 3.8. We can only suspect a bug here, because we see it works in SQLite 3.27.

Here is another approach that may work for you. I am selecting a random word ID with each category using a LIMIT subquery. Then I join the word data.

select c.category_id, c.category, w.word, w.word_category, w.word_id
from
(
  select
    c.category_id, c.category,
    (
      select word_id
      from words w
      where w.category_id = c.category_id
      order by random()
      limit 1
    ) as word_id
  from categories c
) c
join words w on w.word_id = c.word_id
order by c.category_id;

Demo: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=7630068aa3ba71e70b7a6bddd83047dc