selecting a certain number of records? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

selecting a certain number of records?

Author

Topic

a0f6459
Starting Member

4 Posts

Posted - 2008-04-16 : 17:25:22

Hi,

Here is a basic example of the issue I am having:

Table 1 columns - name, address, zipcode, favorite food

For table 2 I want to find how many zipcodes exists and also take 20% of the count

Table 2 columns - based off Table 1 contains zipcode, count(zipcode) as ct, count(zipcode) * .20 as perc_ct

For example:

zipcode ct perc_ct
83746 10 2
93847 20 4

I want to run a query that will pull any 2 records for 83746 and any 4 records for 93847 from Table 1.

Is this possible?

bfoster
Starting Member

30 Posts

Posted - 2008-04-16 : 23:10:48

SELECT [Name], Address, FavoriteFood, ZipCode
FROM
(
SELECT [Name]
,Address
,FavoriteFood
,ZipCode
,NTILE(5) OVER(PARTITION BY ZipCode ORDER BY NEWID()) AS 'Quintile'
FROM Table1
) z
WHERE Quintile = 5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources