August 26, 2003 at 11:24 am
I am trying to get a SQL statement to work that has been driving me crazy for last day.
my table is called TEST. TEST contain fields AREA Varchar(50), and DisAmount (numeric(18,2)
I need to get the top 20 amounts by area.
If I have 5 areas, I would get 100 records (top 20 amounts). Any ideas? Thanks for any help you can give me.
August 26, 2003 at 12:27 pm
Think something like this might work for you:
select * from test a
where disamount in (select top 20 disamount from testz where area = a.area order by disamount desc)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 26, 2003 at 12:30 pm
You probably caught my typo. These query should have been:
select * from test a
where disamount in (select top 20 disamount from test where area = a.area order by disamount desc)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 26, 2003 at 12:47 pm
Thanks for the reply! I ran the query and while I was running it my CPUs spiked to 100%. 28000 records in test table. The way the query is setup wont it have to go thru 7,540,000 records? Got to be a better way.
August 26, 2003 at 2:37 pm
I would expect this to be a CPU bound process that might cause your CPU to spike, but I'm not sure how you are came up with the 7,540,000 number. I would guess if you have an index on AREA that the correlated sub-query would not take a whole lot of work, and would not require reading/sorting the whole table to return 20 rows. What does your execution plan look like? How long did it take to process your query?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 26, 2003 at 8:58 pm
ghughes,
I tested Greg's statement and it should work fine. The only suggestion that i might make is to provide an alias for the correlated subquery table. But i tried it both ways with no problem.
If you're only going to be dealing with 5 areas, it shouldn't be too difficult for you to use a UNION to combine your rowsets.
So your SELECT should like this.
SELECT TOP 20 * FROM Test
WHERE Area = 'Area 1'
UNION ALL
SELECT TOP 20 * FROM Test
WHERE Area = 'Area 2'
UNION ALL
SELECT TOP 20 * FROM Test
WHERE Area = 'Area 3'
UNION ALL
SELECT TOP 20 * FROM Test
WHERE Area = 'Area 4'
UNION ALL
SELECT TOP 20 * FROM Test
WHERE Area = 'Area 5'
ORDER BY Area, DisAmount DESC
Save that badboy as a stored procedure and you should get the 100 rows that you're looking for each time. Let me know if that works.
August 27, 2003 at 11:08 am
Thanks for all help! I finally got my ultimate goal which was to take the SELECT statement and use it to create a UPDATE SQL Statement which updates a field called top20byarea with a 'Y' when it made it.
Here is how I did it and it was awesume and painful for a novice like myself to have come up with (thank goodness for the many examples found on this site). Here it is...
declare c_top20 cursor local
forward_only
for
select area, disamount
from test
order by area asc, disamount desc
-- open the cursor
open c_top20
fetch next from c_top20
into @areaX, @da
set @savearea=' '
set @vcounter=1
while @@fetch_status = 0
begin
if @savearea<>@areaX
begin
-- area change, reset save value and reset counter
set @savearea=@areaX
set @vcounter=1
end
else
begin
set @vcounter = @vcounter + 1
end
if @vcounter < 21
begin
-- only update first 20 columns for each area
-- if after 20 rows, read through remaining area values
update test
set top20byarea = 'Y'
where current of c_top20
end
fetch next from c_top20
into @areaX, @da
end
close c_top20
deallocate c_top20
The whole process runs in 11 seconds! Thanks for everybodys help! I have appricated it alot!
Edited by - ghughes on 08/27/2003 11:10:45 AM
August 27, 2003 at 12:30 pm
I think if you would have created the following clustered, or nonclusted index on you test table like below, then ran the update command I supplied you would find that it would run fairly fast, possibly even faster than your solution.
create clustered index area_ix on test(area asc, disamount desc)
update test
set top20byarea ='y'
from test a
where disamount in
(select top 20 disamount from test where area = a.area order by disamount desc)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Edited by - greg larsen on 08/27/2003 12:34:16 PM
Edited by - greg larsen on 08/27/2003 12:35:36 PM
Gregory A. Larsen, MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply