PostgreSQL - Only different values in fields

Question

I have a table called questions containing a single column filled with Exam question IDs. Let's say:

Q1
Q2
Q2
...
Qn

Now I'd like to pick all the combinations of three questions, something like:

1, 2, 3
...
2, 5, 6
4, 7, 1
...
9, 6, 8

And seclect a subset of them made of rows that have globally unique values only. In the previous case:

1, 2, 3
9, 6, 8

Because the other two records contain 2 and 1 which are both contained in the (1, 2, 3) record.

How can this be achieved in SQL? The purpose is to create, let's say, 8 exams made of questions that are all different by each other.

Are you using MySQL or PostgreSQL ? You should be specific regarding the DBMS you're using... — Caldazar, Jun 25 '18 at 10:05
So an exam consists of three questions? And you don't care which combinations get picked, as long as no exams have any question in common? Then the easiest would be 1,2,3 - 4,5,6 - 7,8,9 - ... Would that be a solution for you? — Thorsten Kettner, Jun 25 '18 at 10:41

kiran gadhe · Answer 1 · 2018-06-25T10:33:02.430

0

lets consider question table has

Table : questions

Qid
1
2
3
.
n

Now if you want to select only 8 (of three questions) distinct randomized subsets then

SELECT FO,STRING_AGG(QID , ',') 
FROM (SELECT Qid , (Qid / 3) :: INT AS FO  FROM QUETIONS   
      ORDER BY RANDOM ()
      LIMIT 8*3 )
GROUP BY FO

edited Jun 25 '18 at 10:33

answered Jun 25 '18 at 09:56

kiran gadhe

733
3
11

Why `LIMIT 8*6` instead of `LIMIT 8*3` ? – IVO GELOV Jun 25 '18 at 10:31
actually its typo – kiran gadhe Jun 25 '18 at 10:32

score 0 · Answer 2 · answered Jun 25 '18 at 10:52

A trivial approach is like this (the CROSS joins will be slow - e.g. for 100 questions may take half a minute)

  SELECT Q1.ID AS Q1, Q2.ID AS Q2, Q3.ID AS Q3
  FROM Questions AS Q1, Questions AS Q2, Questions AS Q3
  WHERE Q1.ID <> Q2.ID AND Q1.ID <> Q3.ID AND Q2.ID <> Q3.ID
  ORDER BY RANDOM() LIMIT 8

However, there are more clever ways to do it - this answer is for MS SQL Server but can be adapted to PostgreSQL

score 0 · Answer 3 · answered Jun 25 '18 at 14:47

Give your questions numbers: 1, 2, 3, 4, 5 ... n. Then divide by 3 dismissing the rest: 0, 0, 0, 1, 1, ... n/3 to get groups of three. It's up to you how to number the questions, e.g. by ID (least ID is record #1, next ID is record #2, ...) or randomly. Here is an example for randomly:

select *, (row_number() over (order by random()) - 1 ) / 3 as grp
from questions
order by grp;

Keep the result as is or pivot it to get one row per grp with three columns instead, e.g.

select
  max(case when rn % 3 = 0 then q end) as q1,
  max(case when rn % 3 = 1 then q end) as q2,
  max(case when rn % 3 = 2 then q end) as q3
from 
(
  select *, row_number() over (order by random()) - 1 as rn
  from questions
) numbered
group by rn / 3
order by rn / 3;

PostgreSQL - Only different values in fields

3 Answers3