SELECT TOP N with variable

Question

I have the following data in a SQL table

Temp table variable @RndQuesnCount contains this data,

Recid conceptID MinDisplayCount
1      3839     2
2      4802     3

Question table : QuesTable

 QuesCompID     Ques_ConceptDtlID
  88         4802
  89         4802
  90         4802
  91         4802
  92         4802
  93         4802

What I would like to show is the min display count for question which is in @RndQuesnCount for the concept id, so now the data should come as below

  QuesCompID    Ques_ConceptDtlID
  88         4802
  89         4802
  90         4802

because conceptid (4802) has min display count 3 in @RndQuesnCount table.

Can anyone help me to solve this problem ?

Answer 1

Simple use of ROW_NUMBER() and a join gets the results, I think. Data setup:

declare @RndQuesnCount table (recid int,conceptid int,mindisplaycount int)
insert into @RndQuesnCount(Recid,conceptID,MinDisplayCount) values
(1,      3839,     2),
(2,      4802,     3)

declare @QuesTable table (QuesCompID int,Ques_ConceptDtlID int)
insert into @QuesTable(QuesCompID,Ques_ConceptDtlID) values
(88,         4802),
(89,         4802),
(90,         4802),
(91,         4802),
(92,         4802),
(93,         4802)

Query:

select
    t.rn,
    t.QuesCompID,
    t.Ques_ConceptDtlID 
from
    @RndQuesnCount rqc
        inner join
    (select *,ROW_NUMBER() OVER (PARTITION BY Ques_ConceptDtlID ORDER BY QuesCompID) rn from @QuesTable) t
        on
            rqc.conceptID = t.Ques_ConceptDtlID and
            rqc.MinDisplayCount >= t.rn

Results:

rn                   QuesCompID  Ques_ConceptDtlID
-------------------- ----------- -----------------
1                    88          4802
2                    89          4802
3                    90          4802

Answer 2

try:

declare @RndQuesnCount int;

select @RndQuesnCount=MinDisplayCount 
from table_variable where conceptID=4802;

set rowcount @RndQuesnCount;

select * from QuesTable;