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
 General SQL Server Forums
 New to SQL Server Programming
 Top 20 Members from to sites

Author  Topic 

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2012-08-01 : 14:53:53
Hi -

I am having a problem in figuring out how to select 20 members from a list of 30 random sites. In my main query I pulled all data, there are more than 1.8 million records. In a second query I pulled (from the main query) 30 random sites that have more than 20 members.....

Now from my random site list I want to get 20 members and each of the 30 sites. There should be a total of 600 records.....

Can anyone please help me do that??

Thanks!

JB

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-01 : 15:42:49
Logically,the following should work - just change the @sites and @records to match your requirements. However, I don't know how efficient this is if there are 1.8 million records, given that it is doing a lot of NEWID() function calls.
CREATE TABLE #tmp(SITE INT, id INT);
INSERT INTO #tmp VALUES
(1,1),(1,2), (1,3), (1,4), (1,5),
(2,1),(2,2), (2,3), (2,4), (2,5),
(3,1),(3,2), (3,3), (3,4),
(4,5)

DECLARE @sites INT = 1, @records INT = 5;

;WITH c1 AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY SITE ORDER BY NEWID()) AS RN,
COUNT(*) OVER (PARTITION BY SITE) AS N
FROM
#tmp
),
c2 AS
(
SELECT TOP (@sites) SITE FROM c1 WHERE N >= @records GROUP BY SITE ORDER BY NEWID()
)
SELECT c1.* FROM c1 INNER JOIN c2 ON c1.site = c2.site
WHERE c1.RN <= @records;

DROP TABLE #tmp;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-01 : 16:26:22
[code]
SELECT Site,
IDENTITY(int,1,1) AS SiteID
INTO #Sites
FROM YourTable
GROUP BY SiteID
HAVING COUNT(DISTINCT MemberID)>=20

SELECT *
FROM #Sites s
CROSS APPLY (SELECT TOP 20 MemberID
FROM table
WHERE Site = s.Site
ORDER BY (ABS(CAST((BINARY_CHECKSUM(MemberID, NEWID())) as int)) % 100) < 10
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -