variable TOP value?

6 Jan 2006 5:26 PM

Jason

Here's a programming puzzle for you gurus :-)

1) Is there any way to accomplish something like this in SQL?

select top @topcount * from mytable

I cannot use dynamic SQL because that will lock my stored procedure.
I do not have a column in the SQL query that I can use to compare with (like
"WHERE CURRENT_COLUMN > @topcount")

2) What I'm thinking might be an option is something like this:

select CurrentRow() as topcount, * from mytable
where topcount < @topcount

I remember reading something about a non-ANSI-standard sql statement that
creates
a column as an iterating recordcount for each row in the result set in sql
server magazine about a year ago, but I lost that magizine.

Does anyone know what I am referring to?

3) A final way that I've tried to accomplish this is by doing a subquery
counting all the rows prior to the current row in the table... as displayed
here:

select
    a.i as CurrentValue,
    (select count(b.i) from mytable b where b.i <= a.i) as CurrentRow
from mytable a
where CurrentRow < @topcount

but this KILLS my performance because it has to run an extra count()
aggregate for every row...    with a table of more than 400 rows, this is
unacceptably long.

Anybody have ideas?

Thanks,

-- Jason

6 Jan 2006 5:37 PM

Mike

Hi,

All you need to do is call

SET ROWCOUNT @topcount

Then run your SQL statement

Select * from mytable

This will return the number of records specified in @topcount. SQL Server
simple stops processing after the specified number.

Regards

- Mike

---------------------------------------------------------------------------------
http://www.cogitar.net"> Cogitar Software. ( http://www.cogitar.net )
http://www.web-dominion.co.uk Web-Dominion. (Web Design and hosting )
http://www.shop-dominion.com (senery landscape picture gallery)
---------------------------------------------------------------------------------

Show quote

"Jason" <ja***@acd.net> wrote in message
news:ve6dnUNHErShNSPeRVn-jg@giganews.com...
> Here's a programming puzzle for you gurus :-)
>
> 1) Is there any way to accomplish something like this in SQL?
>
> select top @topcount * from mytable
>
> I cannot use dynamic SQL because that will lock my stored procedure.
> I do not have a column in the SQL query that I can use to compare with
> (like
> "WHERE CURRENT_COLUMN > @topcount")
>
> 2) What I'm thinking might be an option is something like this:
>
> select CurrentRow() as topcount, * from mytable
> where topcount < @topcount
>
> I remember reading something about a non-ANSI-standard sql statement that
> creates
> a column as an iterating recordcount for each row in the result set in sql
> server magazine about a year ago, but I lost that magizine.
>
> Does anyone know what I am referring to?
>
> 3) A final way that I've tried to accomplish this is by doing a subquery
> counting all the rows prior to the current row in the table... as
> displayed here:
>
> select
>    a.i as CurrentValue,
>    (select count(b.i) from mytable b where b.i <= a.i) as CurrentRow
> from mytable a
> where CurrentRow < @topcount
>
> but this KILLS my performance because it has to run an extra count()
> aggregate for every row...    with a table of more than 400 rows, this is
> unacceptably long.
>
> Anybody have ideas?
>
> Thanks,
>
> -- Jason
>
>
>
>

6 Jan 2006 5:40 PM

Anith Sen

1. In SQL 2005, yes. In SQL 2000, you will have to use other mechanisms that
simulate ranking to achieve that.

2. I think you are referring to ROW_NUMBER() & it is not available in SQL
2000. You will have to use some ranking approach to do it. See KBA: 186133

3. Having a correlated query is generally not a performance hog for small
tables like the one you have with 400 rows. If so it occurs consider taking
a look at indexes/statistics in your table schema.

If it is causing genuine performance issue, you could consider some
alternatives, use a table of numbers to get the rank, an indexed view with
the rank in it or use another SQL formulation ( like using a self join ).
There are several of them available in the archives of this newsgroup.

--
Anith

6 Jan 2006 5:41 PM

Trey Walpole

assuming this is sql2000, as sql2005 can use a top variable...

in sql2000, you can set the rowcount with a variable:
e.g.

set rowcount @topcount

select <column list>
from mytable
where <criteria>
order by <top criteria>

set rowcount 0

Jason wrote:
Show quote

> Here's a programming puzzle for you gurus :-)
>
> 1) Is there any way to accomplish something like this in SQL?
>
> select top @topcount * from mytable
>
> I cannot use dynamic SQL because that will lock my stored procedure.
> I do not have a column in the SQL query that I can use to compare with (like
> "WHERE CURRENT_COLUMN > @topcount")
>
> 2) What I'm thinking might be an option is something like this:
>
> select CurrentRow() as topcount, * from mytable
> where topcount < @topcount
>
> I remember reading something about a non-ANSI-standard sql statement that
> creates
> a column as an iterating recordcount for each row in the result set in sql
> server magazine about a year ago, but I lost that magizine.
>
> Does anyone know what I am referring to?
>
> 3) A final way that I've tried to accomplish this is by doing a subquery
> counting all the rows prior to the current row in the table... as displayed
> here:
>
> select
>     a.i as CurrentValue,
>     (select count(b.i) from mytable b where b.i <= a.i) as CurrentRow
> from mytable a
> where CurrentRow < @topcount
>
> but this KILLS my performance because it has to run an extra count()
> aggregate for every row...    with a table of more than 400 rows, this is
> unacceptably long.
>
> Anybody have ideas?
>
> Thanks,
>
> -- Jason
>
>
>
>

6 Jan 2006 5:48 PM

ML

A quick one: use SQL 2005. :)

Seriously, try using ROWCOUNT:

set rowcount 5
select <column list>
from <table>
order by <sort column>
set rowcount 0

Look up more details in Books Online.

ML

---
http://milambda.blogspot.com/

6 Jan 2006 9:48 PM

Louis Davidson

Two things I might add:

1. What do you mean "lock" your stored procedures?

2. I agree with Anith,

> select
> a.i as CurrentValue,
> (select count(b.i) from mytable b where b.i <= a.i) as CurrentRow
> from mytable a
> where CurrentRow < @topcount

This shouldn't harm performance, though this is not valid syntax to
reference back CurrentRow:

create table mytable (i int)
insert into mytable values (1)
insert into mytable values (2)
insert into mytable values (3)
insert into mytable values (4)
insert into mytable values (5)
insert into mytable values (6)

declare @topcount int
set @topCount = 1

select
    a.i as CurrentValue,
    (select count(b.i) from mytable b where b.i <= a.i) as CurrentRow
from mytable a
where CurrentRow < @topcount

In sql server:

declare @topcount int
set @topCount = 3

select *
from (select a.i as CurrentValue,
            (select count(b.i) from mytable b where b.i <= a.i) as
CurrentRow
         from mytable a
) as withCounts
where CurrentRow < @topcount

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

Show quote

"Jason" <ja***@acd.net> wrote in message
news:ve6dnUNHErShNSPeRVn-jg@giganews.com...
> Here's a programming puzzle for you gurus :-)
>
> 1) Is there any way to accomplish something like this in SQL?
>
> select top @topcount * from mytable
>
> I cannot use dynamic SQL because that will lock my stored procedure.
> I do not have a column in the SQL query that I can use to compare with
> (like
> "WHERE CURRENT_COLUMN > @topcount")
>
> 2) What I'm thinking might be an option is something like this:
>
> select CurrentRow() as topcount, * from mytable
> where topcount < @topcount
>
> I remember reading something about a non-ANSI-standard sql statement that
> creates
> a column as an iterating recordcount for each row in the result set in sql
> server magazine about a year ago, but I lost that magizine.
>
> Does anyone know what I am referring to?
>
> 3) A final way that I've tried to accomplish this is by doing a subquery
> counting all the rows prior to the current row in the table... as
> displayed here:
>
> select
>    a.i as CurrentValue,
>    (select count(b.i) from mytable b where b.i <= a.i) as CurrentRow
> from mytable a
> where CurrentRow < @topcount
>
> but this KILLS my performance because it has to run an extra count()
> aggregate for every row...    with a table of more than 400 rows, this is
> unacceptably long.
>
> Anybody have ideas?
>
> Thanks,
>
> -- Jason
>
>
>
>