TOP 5 for each County - SQL Server Forums

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

Transact-SQL (2000)

TOP 5 for each County

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2004-09-23 : 04:43:14

Somehow I lost it. I like set based, but now I'm stuck on this 'problem'. Here's a simplification.

I have a table tblCountyPrices

with columns County, LotId, Price

Now I want for each county the 5 LotId's with the highest prices (forget about ties)

SO My result set would be:

Orange, 1562, 200
Orange, 1623, 190
Orange, 1561, 190
Orange, 1672, 170
Orange, 1233, 150
Bocate, 3653, 190
Bocate, 2563, 180
Bocate, 3923, 170
Bocate, 1623, 165
Bocate, 2232, 150
Diego, 3564, 210

etc.

I tried something like


SELECT c.CountyName, countyp.* FROM tblCounty c
JOIN
(
SELECT TOP 5 cp.County, cp.LotId, cp.Price FROM tblCountyPrice cp1 ON cp1.County = c.CountyId
) countyp on 1 = 1

But I reckon I have to make crossjoins or something like that?

Henri
~~~~
It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float.
-Al Pacino

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 05:20:34

try this:


select 	*
from	tblCountyPrices CP
where LotId in (select top 2 LotId from tblCountyPrices where cp.County = County)
order by County

Go with the flow & have fun! Else fight the flow

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2004-09-23 : 05:35:05

Mladen , I failed to recognize this as a solution to my question.

What does it do? How does this give me 5 lotprices for every county?

Please, can you explain it to me?

Henri
~~~~
It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float.
-Al Pacino

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 06:05:29

well change
where LotId in (select top 2 LotId from tblCountyPrices where cp.County = County)
to
where Price in (select top 5 Price from tblCountyPrices where cp.County = County order by Price desc)

and then inner join that whole query to County table. Should work....

Go with the flow & have fun! Else fight the flow

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2004-09-23 : 06:32:37

Mladen,

It had some tuning (because of a slightly different situation), but I got the freaky thing to work. Now my only problem is: It's killing the server! Somehow it takes a 110% of the server (with that I mean that the whole server gets sluggish)

thx anyway!

Henri
~~~~
It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float.
-Al Pacino

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 07:20:55

well indexing is important.... do you have any indexes on your tables?

there is another method:
Select *
From tblCountyPrices CP
Where (Select count(1) From tblCountyPrices Where cp.County = County and Price <= CP.Price)<=5
Order By County, Price Desc

and then join to that.

how many rows do you have in your table?

Go with the flow & have fun! Else fight the flow

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2004-09-23 : 07:31:31

A few million records and no index, in this case I was showing an administrator the last 5 logs of specific group of users.

Thx for helping me out (I needed this functionality for several things), the thing was, I said it could be done without cursors. The only thing is, that a cursor worked a lot faster. (for each user in a group show the last 5 logrecords).

But I'm happy, I know what I needed to know, and it works!

Henri
~~~~
It's taken a long time, but I've just about learned how to quit flapping my arms and float. It's good to float.
-Al Pacino

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-23 : 07:36:57

ok, great

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources