Is there any way to select a random, top "n" no: of records from a table so that records with certain values are compulsory..
this is the view ..
CREATE VIEW dbo.QN_RANDOM_ORDER
SELECT TOP 15 CTestQuestionId, CTestQBQuestionComp
WHERE (CTestId = 4)
ORDER BY CTestQBQuestionComp DESC, NEWID()
Here records with "CTestQBQuestionComp=1" is compulsory. the condition is that if we have "n" no: of questions to be selected, and we have enough no: of qns with CTestQBQuestionComp=1, others will not be selected.
using above view compulsory records are listed first and i do not want that. Again we can randomly select from this view to avoid this, here the problem is no: of qns to be selected are not fixed.
thanks in Advance