1

I need to fetch 12 questions from my table. I have a field called "bucket" which might have duplicate values. While fetching, I need only unique bucket values to be fetched and the number of rows has to be 12.

This is my query:

select *
from (
    select
        DISTINCT ON (q.bucket) bucket,
        row_number() over (partition by dl.value order by random()) as rn,
        row_number() over (partition by dl.value, LOWER(qc.value) = LOWER('general') order by random()) as rnc,
        dl.value, qc.value as question_category,
        q.question_text, q.option_a, q.option_b, q.option_c, q.option_d,
        q.correct_answer, q.image_link, q.question_type
    from
        questions_bank q
        inner join
        question_category qc on qc.id = q.question_category_id
        inner join
        sports_type st on st.id = q.sports_type_id
        inner join
        difficulty_level dl on dl.id = q.difficulty_level_id
    where st.game_type = lower('cricket') and dl.value in ('E','M','H')
) s
where
    (value = 'E' and rnc <= 3 and LOWER(question_category) != LOWER('general')) or
    (value = 'E' and rnc <= 3 and LOWER(question_category) = LOWER('general')) or
    value = 'M' and rn <= 4 or
    value = 'H' and rn <= 2;

Can anyone please tell me what am I doing wrong here? It sometimes doesn't return 12 rows. This happens whenever a same bucket value is found. I think, whenever distinct is applied that duplicate row's value is removed. Hence, when I do rn<=4 it is not able to find say, 3. Thus returning only 3 rows instead of 4.

So, I need to apply distinct on bucket first and then get row_numbers. How shall I do that?

Ankita Gupta
  • 155
  • 2
  • 14
  • What do you expect this to do?? **where (value = 'E' and rnc <= 3 and LOWER(question_category) != LOWER('general')) or (value = 'E' and rnc <= 3 and LOWER(question_category) = LOWER('general'))** If you say != LOWER('general') OR = LOWER('general'), you can take it off – nacho Oct 04 '17 at 10:30
  • Basically, I am doing a lot of other things along with getting distinct values of bucket field. I need 3 easy questions from general category, 3 easy questions from non-general categories, 4 medium questions and 2 hard questions. In total 12 questions. These values of 3,3,4,2 can vary. – Ankita Gupta Oct 04 '17 at 10:36
  • Yes, but in that **where clause** you are doing a **TRUE OR FALSE**, which it's allways TRUE. You can leave it this way: **where value = 'M' and rn <= 4 or value = 'H' and rn <= 2;** – nacho Oct 04 '17 at 10:41
  • What about easy questions then? – Ankita Gupta Oct 04 '17 at 10:47
  • What i am trying to say itś that that clause it's allways TRUE. If you put **where a!=1 or a=1**, that becomes allways TRUE because a only can be equal or disctint. In the same way as if you put **where a!=1 and a=1** it becomes allways FALSE. That means that the where clause as you have it doesn't make any sense. – nacho Oct 04 '17 at 10:55
  • My issue, that I have asked is something else. Even if the where condition is redundant, I need distinct buckets for each set. – Ankita Gupta Oct 04 '17 at 12:12

0 Answers0