Semi-random select - SQL Server Forums

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

SQL Server Development (2000)

Semi-random select

Author

Topic

amiller
Starting Member

4 Posts

Posted - 2003-06-23 : 05:15:28

hi, i need to select n random records from m categories/groups within a table. can anyone suggest a better query strategy than the following one:

select top n field1,field2 from table
where category = 'xxx'
order by NEWID()
union
select top n field1,field2 from table
where category = 'yyy'
order by NEWID()
....
and so on until records from all m categories are selected.

thanks

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-23 : 06:23:33

You can not have the following line in an SQL statment.

SELECT TOP @VAR * FROM myTABLE

something like the following might help you.

-- Some dummy records

CREATE TABLE #TEST(FIELD1 INT, FIELD2 INT, CATEGORY CHAR(3))

INSERT INTO #TEST(FIELD1, FIELD2, CATEGORY)
SELECT 1, 1, 'XXX'
UNION ALL
SELECT 2, 2, 'ZZZ'
UNION ALL
SELECT 3, 3, 'YYY'
UNION ALL
SELECT 4, 4, 'XXX'

-- Dynamic SQL statment

DECLARE @VAR INT
SET @VAR = (SELECT CONVERT(INT, RAND(CONVERT(BINARY(4),NEWID()))*1000))

DECLARE @SSQL VARCHAR(2000)
SET @SSQL = ''

SELECT @SSQL = @SSQL + 'SELECT TOP ' + CAST(@VAR AS VARCHAR(20)) + ' FIELD1, FIELD2 FROM #TEST WHERE CATEGORY = ''XXX'''

EXEC(@SSQL)

You can change the number in red to suit the raneg of radnom number you requite for your exercise. The above code generates 1000 random integers.

CONVERT(INT, RAND(CONVERT(BINARY(4),NEWID()))is from another SQLTeam member (thanks Page47)

Good Luck!

Edited by - Amethystium on 06/23/2003 06:25:24

amiller
Starting Member

4 Posts

Posted - 2003-06-23 : 15:10:49

hi, thanks for the advice. whilst developing ideas i tend to write adhoc queries in asp or coldfusion and run them from my computer and then turn them into sp's on the sql server once i've got a better idea of how i'm going to do something. in this case n was a coldfusion variable, so was set outside sql server, but i realise now that i would have run into the problem you mentioned once i tried turning it into a sp.

the app i'm building is an online testing system which needs to select questions randomly from a database, but dynamically set the proportion of questions coming from various topic areas based on the user's previous performance on the system. given that the proportion of questions from each topic area is set dynamically and in some queries there could be 100+ topics, i am slightly uncomfortable with having to union separate select statements for each topic, but i can't think of any other solution. any ideas of how this could be acheived more efficiently would be most appreciated. the query itself turns out to be very similar to stratified random sampling, so if anyone has developed a cool way of doing this that might help...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources