SQL Server - Select N random rows from 2 tables

Question

I am trying to randomly select N amount of rows from 2 tables. My first table looks like this:

q_id | question_text
-----------------
  1  | What am I doing?
  2  | Who are you?
  3  | Hmm?

The table I'm joining to it looks like this:

a_id | q_id | answer_text
-------------------------
  1  |   1  | Nothing
  2  |   1  | Something
  3  |   2  | Mike
  4  |   2  | Steve
  5  |   2  | Jon
  6  |   3  | Yup
  7  |   3  | Nope

I want to output a random question with their answers. So if I have 50 rows, I could select, for example, the top 3 and it would output 3 random questions with however many answers there are.

This is the query that I've been working but when I use the TOP(2), it only grabs the top 2 answers and not top 2 questions:

Query:

SELECT TOP(2) q.q_id, q.question_text, a.answer_text
FROM question q 
INNER JOIN answers a ON q.q_id= a.q_id

Current results:

2  |  Who are you?  | Mike, Steve

Expected results:

1  | What am I doing? | Nothing, Something
3  | Who are you?     | Mike, Steve, Jon

If you are trying to randomly select questions, why are you using TOP? This will always give you the same three questions. — Lloyd Banks, Mar 03 '16 at 18:55
I don't get your question at all. Your sample query would output 2 rows, not 1, and `'Nothing, Something'` is not in your example values for `answer_text`. — Gordon Linoff, Mar 03 '16 at 18:57
I agree, are you actually wanting one row for each answer or are you wanting a concatenated string of all the possible answers in one row per question? — Devon Bessemer, Mar 03 '16 at 18:58
I think you need to use [for xml path](http://stackoverflow.com/questions/15477743/listagg-in-sqlserver) to combine multiple rows into one (for all answers), and then use a to [order by newID()](http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table) and then your top 2 would work. — xQbert, Mar 03 '16 at 19:02
I'm confused with your question. Do you want 3 random questions, each with 3 random answers? That's common if you are building a test from a test bank — Code Different, Mar 03 '16 at 19:05

score 1 · Answer 1 · answered Mar 03 '16 at 19:05

This will get you closer - note it won't magically comma-delimit the answers and put them on one line, but it will at least give you two full questions. You can (and probably should) do the comma-delimited stuff in the presentation layer.

  SELECT
    q.q_id,
    q.question_text,
    a.answer_text
  FROM question q
  INNER JOIN answers a ON q.q_id = a.q_id
  WHERE q.q_id IN (SELECT TOP 2 q_id FROM question);

If you come up with a more random way of designating the question ID's you can just substitute it into the WHERE q.q_id IN ... subquery.

dfundako · Answer 2 · 2016-03-03T19:16:37.777

With this answer, it will generate 3 random numbers between the range you choose, select the questions associated with those values, and then provide the answers from the answer table with those id's. The problem with this answer as is, is that it can potentially generate duplicate values. This could be solved with a union instead.

DECLARE @MIN INT = 1
DECLARE @MAX INT = 100
DECLARE @rand1 INT =  (SELECT CAST(((@max-@min-1) * RAND() + @min) AS INT))
DECLARE @rand2 INT =  (SELECT CAST(((@max-@min-1) * RAND() + @min) AS INT))
DECLARE @rand3 INT =  (SELECT CAST(((@max-@min-1) * RAND() + @min) AS INT))

SELECT q.question_text, a.answer_text
FROM question_table q 
INNER JOIN answer_table a
    ON q.q_id = a.q_id
WHERE q.q_id IN(@rand1, @rand2, @rand3)

ORDER BY q.q_id, a.a_id

Lee · Answer 3 · 2016-03-03T19:46:53.567

You need random quesitons, then join to the answers. Joining to the answers it trivial. To get the random questions, you can use the ID's since they are integers, but if you used an identity, then they may not be contiguous. Therefor, depending on the version of SQL Server, you can use the row_number function:

select q_id
from
(select 
  row_number() over(ORDER BY q_id) as Row,
  q_id
from questions) q
 where Row in ( (select CAST(RAND() * (select COUNT(q_id) from questions) + 1 as int)), 
    (select CAST(RAND() * (select COUNT(q_id) from questions) + 1 as int)))

Since we are pulling two random numbers, you should get two records. Then just join to get the rest of the data

score -1 · Answer 4 · answered Mar 03 '16 at 19:21

-1

You can use newID() to select a random number of questions, as follows:

declare @n int = 2 --set number of questions here

;with Question as (select top (@n) * from QuestionTable
                   order by newID())

select * from Question a
left join AnswerTable b
on a.q_id = b.q_id

answered Mar 03 '16 at 19:21

APH

4,109
1
25
36

Although it's the "standard" way of doing it, it's highly not recommended, because behind the scene you associate a GUID for every row, and the sorting can have high cost (CPU, memory and I/O). consider reading this: https://msdn.microsoft.com/en-us/library/cc441928.aspx . – Shahar Gvirtz Mar 03 '16 at 19:29
Sure, but in this case the tables in question sound relatively small, and the `newID` solution is much simpler and more flexible than the proposed solutions using `rand`. Since the question involves selecting a variable number of rows, I think a solution that allows that variable to be easily adjusted is most appropriate. – APH Mar 03 '16 at 20:54

SQL Server - Select N random rows from 2 tables

4 Answers4