November 21, 2008 at 11:55 am
Hello All,
I'm trying to figure out a way to just return a random list of "customers" from a test table. I've been trying to work with a simple call like:
SELECT TOP 100 CompanyName FROM DemoData ORDER BY NEWID();
Which works GREAT when not in a FunctionTable. I've also tried things like RAND(), but it throws the same error.
I'd love to use a Stored Procedure, or put the ORDER BY in my client environment, but both are not available as I'm using a BusinessObjects Universe. So, no ORDER BY or EXEC calls. Therefore, Function Tables are my solution.
If anyone has an idea on this I'd really appreciate a tip.
THANKS.
November 21, 2008 at 12:06 pm
GOT IT !!!!! 🙂
please hold for details.
November 21, 2008 at 12:20 pm
loop the NEWID() field through a VIEW against the raw data.
CREATE VIEW [mlowden].[dbo].[v_Xtreme_RandomCompanyNames]
AS
SELECT CompanyName, NEWID() AS RandomSortBy FROM mlowden.dbo.Xtreme_Customers
then just use the VIEW as your TABLE in the function
CREATE FUNCTION [mlowden].[dbo].[fn_Xtreme_RandomCompanyNames] ( @iTopN int )
RETURNS @Companies TABLE ( CompanyName VarChar(50) )
AS BEGIN
-- start building the output table
INSERT INTO @Companies
SELECT CompanyName
FROM [mlowden].[dbo].[v_Xtreme_RandomCompanyNames]
ORDER BY RandomSortBy;
DECLARE @iLoop INT;
SET @iLoop = 0; -- append data until it's big enough for return
WHILE ( (SELECT COUNT(CompanyName) FROM @Companies) <= @iTopN )
BEGIN
INSERT INTO @Companies
-- append the @iLoop number to the end to get "CompanyName (22)"
SELECT (CompanyName+' ('+CONVERT(VarChar(10),@iLoop)+')') AS CompanyName
FROM [mlowden].[dbo].[v_Xtreme_RandomCompanyNames]
ORDER BY RandomSortBy;
SET @iLoop = @iLoop+1;
END
RETURN
END
Then, when you call the function table, it will "built" the source set and return as many records as you request, in a random order. as a bonus, because of the way I built this, the first set of records always comes first, but randomly, then the Nth sets (suffixed by (N)) append at the end of the first set. So you will only see the "(N)" at the end of the company name if you happen to select that many.
SELECT CompanyName FROM [mlowden].[dbo].[fn_Xtreme_RandomCompanyNames](1000)
YAY!!!!!!!!!
November 21, 2008 at 12:26 pm
Look up TABLESAMPLE in BOL.
November 21, 2008 at 12:52 pm
COOL.
i've never seen TABLESAMPLE before. but this looks nice. it seems to be unreliable on the number of rows that come back, but i suppose i could just use a TOP N on top of the selection too.
i read this up at: http://msdn.microsoft.com/en-us/library/ms189108.aspx
thanks.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply