Selecting certain records + random records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting certain records + random records

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-07-04 : 11:24:00

This one has totally stumped me so I'd love to know whether it is actually possible...

I have a table that holds multiple choice questions for an online quiz. I have a stored procedure that retrieves a given number of questions from the table, and passes them onto the ASP.NET engine for display.

Now somebody has requested the stored proc be expanded because some questions should ALWAYS be pulled out of the table, followed by a random selection of the remaining questions. I have now added a bitfield column to the table, called "ALWAYSSELECTED".

Can anyone tell me if is possible to achieve the following (in SQL Server 2000):

Given we require a total of N questions to be retrieved, all having a FK value of @skillID


  Select all the questions from the questionTable where ALWAYSSELECTED = true and skillID = @skillID
  If the total number of rows selected is less than N then 
     RANDOMLY select the remaining questions until N questions have been retrieved.

Questions selected in the random pool should not already be selected in the first part (i.e. no duplicates!).

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-04 : 11:38:36

something like:


declare @rowcount int, @N int
select @N = 10
set rowcount @N
select * into #temp from questionTable where ALWAYSSELECTED = 1 and skillID = @skillID
select @rowcount = @@rowcount
if @rowcount < @N 
begin
     set rowcount @N - @rowcount
     insert into #temp(columns)
     select columns 
     from   questionTable t1   
     where  not exists (select * from #temp where id = t1.id)
     order by newid()
     set rowcount 0
end 
select * from #temp

Go with the flow & have fun! Else fight the flow

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-04 : 11:49:59

Write a stored procedure to
1. Select the Compulsary Qs to a Temp Table (as u do)
2. Find the # of Compulsay records
3. Use the following to set the next Output : set rowcount N - Compulsary records (after checking whether
value is > 0 )
4. Select ... where ALWAYSSELECTED = False ... Order By NewID()
5. Insert the results in above 4. to the same temp table in 1.
6. Select the results from Temp table
7. Drop the temp table
Edit :
7. Set rowcount = 0

Srinika

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-07-06 : 02:34:20

Hi guys. Thanks for you input.
Sorry for not getting back sooner; something urgent cropped up yesterday.

I've not used temp tables before in SQL Server. Are these common practice?

I was thinking the table would be inner-joined to a nested select statement or something. If possible, what are the performance differences in doing inner-joins as opposed to the temp table approach?

T-SQL seemed so easy in the training videos...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-06 : 04:49:09

#temp tables are often used and are preety cool

you shoul dalso look into a table variable in BOL.

Sql IS easy. Concepts are hard!

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources