• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Random selection of records

hi

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
AS
SELECT   TOP 15 CTestQuestionId, CTestQBQuestionComp
FROM         dbo.WHIRE_CTestQuestion
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.

Any idea

thanks in Advance
Riyasjef
0
riyasjef
Asked:
riyasjef
1 Solution
 
RaisinJCommented:
If the number of questions being pulled back is dynamic, maybe you should be using a Stored Procedure instead of a view to pull this data.  What does "CTestID" represent in the WHIRE_CTestQuestion table?  From what I understand, you trying to pull records which contain a certain ID.  If the record count in not enough, then pull back more records with different ID values, if the record count in enough, then pull back no more records.

Am I understanding this correctly?
0
 
illCommented:
-- you nedd unin i think, but i'm not sure about the synatax
CREATE VIEW dbo.QN_RANDOM_ORDER
AS
select top 15 * from
(
SELECT   TOP 15 CTestQuestionId, CTestQBQuestionComp
FROM         dbo.WHIRE_CTestQuestion
WHERE     (CTestId = 4 AND CTestQBQuestionComp=1)
UNION
SELECT   TOP 15 CTestQuestionId, CTestQBQuestionComp
FROM         dbo.WHIRE_CTestQuestion
WHERE     (CTestId = 4 )

ORDER BY CTestQBQuestionComp DESC, NEWID()
) as a
0
 
riyasjefAuthor Commented:
hi
"CTestQuestionId" represents the table.
So I have to query twice?

riyasjef
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
illCommented:
-- in first approach yes, sorry not to be more helpful, but i'm in a hurry now( as always).
if i understand correcttly:
if  CTestQBQuestionComp=1 exists, you want them
else display random from CTestQBQuestionComp="n"

-- just a try
SELECT   TOP 15 CTestQuestionId, CTestQBQuestionComp
FROM         dbo.WHIRE_CTestQuestion
WHERE     (CTestId = 4)
ORDER BY case CTestQBQuestionComp when 1 then CTestQBQuestionComp DESC  else NEWID() end
0
 
riyasjefAuthor Commented:
Actually CTestQuestionId is of type "int" and
CTestQuestionComp - bit

here the

Error

---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data type uniqueidentifier to bit is not allowed. Use the CONVERT function to run this query.
---------------------------
OK   Help  
---------------------------


Riyasjef
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What about this:

CREATE VIEW dbo.QN_RANDOM_ORDER
AS
select L.* from
(
SELECT   TOP 15 CTestQuestionId, CTestQBQuestionComp
FROM         dbo.WHIRE_CTestQuestion
WHERE     (CTestId = 4)
ORDER BY CTestQBQuestionComp DESC, NEWID()
) as L
ORDER BY NEWID()

Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now