Select top n rows for each ID where n varies by ID

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 2005 Forums

Transact-SQL (2005)

Select top n rows for each ID where n varies by ID

Author

Topic

duncanwill
Starting Member

20 Posts

Posted - 2012-07-18 : 13:10:40

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 13:48:17

declare @Users TABLE (
UserID INT,
PercentToSelect INT
)

declare @Data TABLE (
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 d1 where d1.UserID = u.UserID ORDER BY NEWID()
)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-18 : 13:48:51

[code]SELECT
*
FROM
#Users u
OUTER APPLY
(
SELECT TOP (u.PercentToSelect) PERCENT *
FROM #Data
WHERE UserID = U.UserID
ORDER BY NEWID()
) AS d[/code]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 13:51:17

Testing it - this doesn't work as it retrieves different results each run.
Doesn't really surprise me as it is being run per row.
Maybe a cross apply thingy - but I don't do them.

:).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

duncanwill
Starting Member

20 Posts

Posted - 2012-07-18 : 16:45:42

Lamprey - that seems to be excellent and more elegant than i hoped!

Like Nigel I have only dabbled with APPLY - i need to brush up my skills i think!

I`ll give it a test and some study...

Many thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources