Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Select itens in groups

Answered Select itens in groups

  • Friday, January 04, 2013 5:58 PM
     
      Has Code
    Sign In to Vote
    0

    Hello,

    I have the following table:

    create table dbo.Questions
(
  Id int not null
    constraint Questions_Id_PK primary key clustered (Id),
  QuestionFormatId int not null,
  DifficultyLevel int not null,
  Duration int not null
);

    How can I select 7 random questions as follows:

      2 Questions with FormatId = 2 AND DifficultyLevel = 1 AND Duration = 4

      2 Questions with FormatId = 4 AND DifficultyLevel = 1 AND Duration = 6

      3 Questions with FormatId = 6 AND DifficultyLevel = 2 AND Duration = 8

    So in the end I would have 7 questions.

    Thank You,

    Miguel

     

All Replies

  • Friday, January 04, 2013 6:05 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Try

    ;with cte1 as (select top (2) * from dbo.Questions where FormatId = 2 and DifficultyLevel = 1 and Duration = 4 

ORDER BY NewID()),

cte2 as (select top (2) * from dbo.Questions 
where FormatId = 4 AND DifficultyLevel = 1 AND Duration = 6

ORDER BY NewID()),

cte3 as (select top (3) * from dbo.Questions

WHERE FormatId = 6 AND DifficultyLevel = 2 AND Duration = 8
ORDER BY NewID())

select * from cte1

UNION ALL

select * from cte2 

UNION ALL

select * from cte3

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


     
  • Friday, January 04, 2013 6:48 PM
     
     Answered
    Sign In to Vote
    0

    SELECT X.question_nbr 
     FROM (
    (SELECT question_nbr
      FROM (SELECT question_nbr, ROW_NUMBER() 
                    OVER (PARTITION BY question_format, difficulty_level, question_duration) AS q_seq
              FROM Questions
             WHERE question_format = 2
               AND difficulty_level = 1
               AND question_duration = 4)
     WHERE q_seq <= 2)
    UNION ALL
    (SELECT question_nbr
      FROM (SELECT question_nbr, ROW_NUMBER() 
                    OVER (PARTITION BY question_format, difficulty_level, question_duration) AS q_seq
              FROM Questions
             WHERE question_format = 4
               AND difficulty_level = 1
               AND question_duration = 6)
     WHERE q_seq <= 2)
    UNION ALL
    (SELECT question_nbr
      FROM (SELECT question_nbr, ROW_NUMBER() 
                    OVER (PARTITION BY question_format, difficulty_level, question_duration) AS q_seq
              FROM Questions
             WHERE question_format = 6
               AND difficulty_level = 2
               AND question_duration = 8)
     WHERE q_seq <= 3)
    ) AS X(question_nbr);

    You can add a randomization to ROW_NUMBER() 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked As Answer by MDMoura Friday, January 04, 2013 7:07 PM
    •  
     
  • Friday, January 04, 2013 7:07 PM
     
     
    Sign In to Vote
    0

    Thank you both for the help,

    Miguel