top 10 zipcodes
-
I have a large table indexed on zip code. I need to come up with a way to get 10 records for each zip code on the table. I can't come up with a simple, efficient way.
-
Do you mean the first ten records in the table or matching some pattern? Or do you want the first ten rows of data matching some zip code?
select top 10 * from item where zip = xxx doesn't work?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
I have a table with several million address records and I am trying to create a sample file. I want to get 10 records for every zipcode on the file.
-
Is there a primary key?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
I'm not really proud of that solution because I don't like cursors but at least it works
.Here is an example based on the pubs database:
declare @t_au_ord table (au_ord tinyint)
declare @t_result table (au_ord tinyint, title_id char(6))
declare @au_ord tinyint
insert @t_au_ord select distinct au_ord from titleauthor
declare C cursor for select au_ord from @t_au_ord
open C
fetch C into @au_ord
while @@FETCH_STATUS = 0
begin
insert @t_result select top 2 au_ord, title_id from titleauthor where au_ord = @au_ord
fetch C into @au_ord
end
close C
deallocate C
select * from @t_result
Bye
gabor
Bye
Gabor -
Try this out. There might be some elaborate set based way to do this as well but this seems to work.
-- temp holding spot for all unique zip codes
declare @zip table (zip nvarchar(10)) -- or however you have it stored
-- table to hold up to 10 records for each zip code
declare @members table (account bigint,
zip nvarchar(10))
insert into @zip (zip)
select distinct zip
from ah_member
where zip is not null
order by zip
declare @zip_code nvarchar(10)
set @zip_code = '0'
while (select count(zip) from @zip where zip > @zip_code) > 0
begin
select top 1 @zip_code = zip
from @zip
where zip > @zip_code
insert into @members(account, zip)
select top 10 account, zip
from ah_member
where zip = @zip_code
end
select *
from @members
order by zip, account
Micahel A. Floyd
Project Manager
American Healthways, Inc.
3841 Green Hills Village Drive
Suite 300
Nashville, TN 37135
mailto:mafloyd@home.com
-
Thanks to everyone who responded. I'm going to look at the various options and decide from there.
-
If your table has a primary key, say pkey, then the following set-based solution will get the first 10 records for each zipcode ordered by the primary key:
select *
from zip z1
where pkey in (select top 10 pkey
from zip z2
where z2.zipcode = z1.zipcode
order by z2.pkey asc)
To select the 10 records randomly, try:
select *
from zip z1
where pkey in (select top 10 pkey
from zip z2
where z2.zipcode = z1.zipcode
order by newid())
-
Thanks, this looks like more of what I wanted.
.Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply