Hi All,
Trying to find an elegant solution for the following:
I have a Data table that includes a userID column and also a corresponding User table.
The user table includes a field that gives an INT value that indicates the percentage of records each user needs to return in the query from the data table. The records to return for each user should be a random selection from each users records inthe data table.
So User 1 may have a level of 50% and user two 80% etc.
I think the following snippet shows roughly what i am trying to achieve... but it is not correct. The results confuse me!
CREATE TABLE #Users(
UserID INT,
PercentToSelect INT
)
CREATE TABLE #Data(
ID INT PRIMARY KEY IDENTITY,
UserID INT,
ProductData varchar(50)
)
INSERT INTO #Users VALUES (1, 80)
INSERT INTO #Users VALUES (2, 50)
INSERT INTO #Data VALUES (1,'Alpha')
INSERT INTO #Data VALUES (1,'Beta')
INSERT INTO #Data VALUES (1,'Gamma')
INSERT INTO #Data VALUES (1,'Delta')
INSERT INTO #Data VALUES (2,'Beta')
INSERT INTO #Data VALUES (2,'Gamma')
INSERT INTO #Data VALUES (2,'Delta')
INSERT INTO #Data VALUES (2,'Alpha')
INSERT INTO #Data VALUES (2,'Pi')
INSERT INTO #Data VALUES (2,'Omega')
SELECT
*
FROM
#Data d
INNER JOIN
#Users u ON
u.UserID = d.UserID
WHERE d.ID IN
(
SELECT TOP (u.PercentToSelect) PERCENT ID FROM #Data ORDER BY NEWID()
)
DROP TABLE #Data
DROP TABLE #Users
Select top n rows for each ID where n varies by ID
Posted - 07/18/2012 : 13:10:40
