Can I Pull multiple subsets from a query? - 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)

Can I Pull multiple subsets from a query?

Author

Topic

cblythe
Starting Member

6 Posts

Posted - 2010-05-18 : 15:39:50

I am stuck on an issue that I think probably has a simple solution but I'm not looking at it the right way.

I have a query that returns all vendors with in 25 or 100 miles of a
property_id /zip code depending on the type of vendor. This works
great, however for some of the zip codes in highly populated areas I
am receiving 1000 rows returned. What'd I'd like to do is return the
first 5 rows for each property_id/zip code combination.

I'm trying to avoid doing something like a cursor loop for each record
in the NewAssignments table, I'd like to just alter the query to
return sets of up to 5 rows per property_id/Zipcode:

Here is the Query as it stands:
[Code]SELECT DISTINCT
r.property_id ,
r.address ,
r.city ,
r.STATE ,
r.zip ,
v.CompanyName ,
v.zip ,
z.zipdest ,
z.Distance ,
v.PreferredVendor
FROM dbo.NewAssignments r
INNER JOIN dbo.tblZipDistances z ON r.zip=z.zipsource
INNER JOIN dbo.tblVendor v ON z.zipdest=v.Zip
WHERE ( ( z.Distance<25
AND PreferredVendor IN ( 'No', 'Do not use' ) )
OR ( z.Distance<100
AND PreferredVendor IN ( 'Yes', 'Yes-QReo' ) ) )
AND v.Active='Y'
ORDER BY r.property_id ,
v.PreferredVendor DESC ,
z.Distance

Here is a sample of the data returned vs what I'd like to see:

What is currently returned				
Property_ID	ZipCode	Vendor	Distance	PreferredVendor
339393	94608	Company 1	39	Yes-QReo            
339393	94608	Company2	10	Yes                 
339393	94608	Company3	34	Yes                 
339393	94608	Company4	53	Yes                 
339393	94608	Company5	56	Yes                 
339393	94608	Company6	72	Yes                 
339393	94608	Company7	72	Yes                 
339393	94608	Company8	85	Yes                 
339393	94608	Company9	0	No                  
340062	14845	Company3	0	No                  
340062	14845	Company7	0	No                  
340062	14845	Company 10	0	No                  
340074	85053	Company3	19	Yes-QReo            
340074	85053	Company4	8	Yes                 
340074	85053	Company3	13	Yes                 
340074	85053	Company7	15	Yes                 
340074	85053	Company 10	19	Yes                 
340074	85053	Company14	20	Yes                 
340074	85053	Company 17	22	Yes                 
340074	85053	Company 21	22	Yes                 
				
				
What I'd like to see				
Property_ID	ZipCode	Vendor	Distance	PreferredVendor
339393	94608	Company 1	39	Yes-QReo            
339393	94608	Company2	10	Yes                 
339393	94608	Company3	34	Yes                 
339393	94608	Company4	53	Yes                 
339393	94608	Company5	56	Yes                 
340062	14845	Company3	0	No                  
340062	14845	Company7	0	No                  
340062	14845	Company 10	0	No                  
340074	85053	Company3	19	Yes-QReo            
340074	85053	Company4	8	Yes                 
340074	85053	Company3	13	Yes                 
340074	85053	Company7	15	Yes                 
340074	85053	Company 10	19	Yes                 
[/code]

I hope this makes sense and I have a feeling I'm just making it too complicated after a couple long days.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-19 : 01:36:24

[code]select
property_id ,
address ,
city ,
STATE ,
zip ,
CompanyName ,
zip ,
zipdest ,
Distance ,
PreferredVendor
from
(
select
row_number() over (partition by Property_ID, ZipCode order by Property_ID, ZipCode) as rownum,
*
from
(
SELECT DISTINCT
r.property_id ,
r.address ,
r.city ,
r.STATE ,
r.zip ,
v.CompanyName ,
v.zip ,
z.zipdest ,
z.Distance ,
v.PreferredVendor
FROM dbo.NewAssignments r
INNER JOIN dbo.tblZipDistances z ON r.zip=z.zipsource
INNER JOIN dbo.tblVendor v ON z.zipdest=v.Zip
WHERE ( ( z.Distance<25
AND PreferredVendor IN ( 'No', 'Do not use' ) )
OR ( z.Distance<100
AND PreferredVendor IN ( 'Yes', 'Yes-QReo' ) ) )
AND v.Active='Y'
)dt1
)dt2
where rownum < 6 -- to get each top 5 records
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

cblythe
Starting Member

6 Posts

Posted - 2010-05-19 : 08:46:42

Thanks that did exactly what I needed.

Placing the row_number() select in the middle is what I couldn't get my head around.

Thanks again for the great help.

Cory

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-19 : 09:02:01

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-19 : 09:47:55

There are many methods to do this
Refer http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources