Use TOP by variable - 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)

Use TOP by variable

Author

Topic

Ali Reza Pooneh
Starting Member

14 Posts

Posted - 2008-08-30 : 01:52:16

Hi all,
I have a table that store info abaout some banner(advertisment):

ID int
Name varchar
Rank int

that Rank is between 1 and 100.

I want to select one randomely by this note that Banner by more Rank select by more chance. (ie: Banner by Rank=50 select 5 times Banner by Rank=10)
for it, I write this:


declare @sum as int
select @sum = Sum(Rank) from Banner 

--Generate Random number
declare @r as float,  @tempSum as int
set @r = Floor(Rand()*@sum)

--calculate and select banner 
declare @i as int
set @i = 1


set @tempSum=0
select @tempSum =  Sum(Rank) from Banner where (ID IN(select top(@i) ID from Banner order by ID)) 

while @tempSum < @r
begin

	set @i = @i+1
	select @tempSum =  Sum(Rank) from Banner where (ID IN(select top(@i) ID from Banner order by ID))

end
 select * from Banner where (ROWCOUNT = @i)

This sql work on Sql Server 2005, But I want to run it on 2000 and has error by TOP clause.
Please help me. A way for use TOP, or other way to get Sum(Rank) of Top (@i) records, Or another way for select one record randomely by notice to Rank.
Thanks All.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-30 : 01:54:32

the TOP (@variable) it is not supported in SQL 2000.

use
set rowcount = @variable

KH
[spoiler]Time is always against us[/spoiler]

Ali Reza Pooneh
Starting Member

14 Posts

Posted - 2008-08-30 : 02:07:35

quote:
Originally posted by khtan

the TOP (@variable) it is not supported in SQL 2000.

use
set rowcount = @variable

KH
[spoiler]Time is always against us[/spoiler]

I use this, But don't work, I write:


declare @i as int
set @i = 1
set @tempSum=0
set ROWCOUNT  @i

SELECT     @tempSum = Sum(Rank)
FROM         Banner

But this print Sum of all records!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-30 : 02:22:19

that's row count of 1 row. The query

SELECT Sum(Rank)
FROM Banner

what rowcount do is return only 1 row of the result set not to sum only 1 row. So your query will basically perform the sum for all the rows in Banner table and return 1 row to you,

What are you trying to achieve here ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 02:34:24

may be this:-

declare @sum as int
select @sum = Sum(Rank) from Banner 

--Generate Random number
declare @r as float,  @tempSum as int
set @r = Floor(Rand()*@sum)

select *
from Banner t
where (select Sum(Rank) from Banner where ID<=t.ID) <@r
order by ID

Ali Reza Pooneh
Starting Member

14 Posts

Posted - 2008-08-30 : 02:40:05

I want to get Sum(Rank);

First only one record, then Sum(Rank) top 2 records , and ...
WHILE (Sum(Rank) of TOP @i records < @var)

Problem is get Sum(Rank) of TOP @i records!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-30 : 02:42:36

sound like running total. Why not doing this in your front end application ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 02:47:48

quote:
Originally posted by Ali Reza Pooneh

I want to get Sum(Rank);

First only one record, then Sum(Rank) top 2 records , and ...
WHILE (Sum(Rank) of TOP @i records < @var)

Problem is get Sum(Rank) of TOP @i records!

isnt this what you're looking for?

declare @sum as int
select @sum = Sum(Rank) from Banner 

--Generate Random number
declare @r as float,  @tempSum as int
set @r = Floor(Rand()*@sum)

select *,(select Sum(Rank) from Banner where ID<=t.ID) as total
from Banner t
where (select Sum(Rank) from Banner where ID<=t.ID) <@r
order by ID

Ali Reza Pooneh
Starting Member

14 Posts

Posted - 2008-08-30 : 03:00:39

quote:
Originally posted by khtan

sound like running total. Why not doing this in your front end application ?

KH
[spoiler]Time is always against us[/spoiler]

Because must run some SQL query, running them in ONE procedure is better than run from application.

Thanks Mr.visakh16. Problem is solved now! Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 03:05:11

quote:
Originally posted by Ali Reza Pooneh

quote:
Originally posted by khtan

sound like running total. Why not doing this in your front end application ?

KH
[spoiler]Time is always against us[/spoiler]

Because must run some SQL query, running them in ONE procedure is better than run from application.

Thanks Mr.visakh16. Problem is solved now! Thanks a lot.

you're welcome

vijeyashobana
Starting Member

9 Posts

Posted - 2008-09-04 : 17:11:22

quote:
Originally posted by khtan

the TOP (@variable) it is not supported in SQL 2000.

use
set rowcount = @variable

KH
[spoiler]Time is always against us[/spoiler]

Thanks for your post. I have a very similar problem. I used

set @lastzip = 1494
set rowCount = @lastzip from From vwSalesTeam v
where v.userid = @userid

instead of

select @lastzip = top 1494 zipcode + ',' From vwSalesTeam v
where v.userid = @userid order by zipcode asc
set @zipcodes = @Keywords

i was working in SQL 2005 but now working in SQL 2000. I get an error message
Msg 170, Level 15, State 1, Procedure usp_xxx, Line 386
Line 386: Incorrect syntax near '='.

Could you pinpoint what error I'm doing here???

Any ideas/suggestions highly appreciated.

Thanks & Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 03:48:36

That should be

select top 1494 @lastzip = zipcode + ',' From vwSalesTeam v
where v.userid = @userid order by zipcode asc
set @zipcodes = @Keywords

Madhivanan

Failing to plan is Planning to fail

vijeyashobana
Starting Member

9 Posts

Posted - 2008-09-05 : 09:18:27

quote:
Originally posted by madhivanan

That should be

select top 1494 @lastzip = zipcode + ',' From vwSalesTeam v
where v.userid = @userid order by zipcode asc
set @zipcodes = @Keywords

Madhivanan

Failing to plan is Planning to fail

Thank you Madhivaanan

Thanks & Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources