Top divisible of ... - 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 divisible of ...

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-27 : 02:32:09

I have this data...

ID - NAME - POSITION
----------------------------------------
1 - A - aa
2 - B - bb
3 - C - cc
4 - D - dd
5 - E - ee
6 - F - ff
7 - F - ff
8 - F - ff
9 - B - bb
10 - B - bb
11 - B - bb
12 - A -aa

Is there a way to querry the top 1 multiple by 3 of records?

jUST an example:
SYNTAX:
TOPMULTIPLEOF(multiplenumber,top)

EX. 1

SELECT TOPMULTIPLEOF(3,1) * from tblAida ORDER BY ID

Result:
ID NAME POSITION
----------------------------------------
1 A aa
2 B bb
3 C cc
***************************************************************

EX. 2
SELECT TOPMULTIPLEOF(3,2) * from tblAida ORDER BY ID

Result:
ID NAME POSITION
----------------------------------------
4 D dd
5 E ee
6 F ff
***************************************************************

EX. 3
SELECT TOPMULTIPLEOF(5,1) * from tblAida ORDER BY ID

Result:
ID NAME POSITION
----------------------------------------
1 A aa
2 B bb
3 C cc
4 D dd
5 E ee
***************************************************************

EX. 4
SELECT TOPMULTIPLEOF(5,2) * from tblAida ORDER BY ID

Result:
ID NAME POSITION
----------------------------------------
6 F ff
7 F ff
8 F ff
9 B bb
10 B bb
***************************************************************

Want Philippines to become 1st World COuntry? Go for World War 3...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-27 : 04:03:15

[code]set nocount on
set rowcount 0

create table #t(ID int not null, NAME char(1) not null, POSITION char(2) not null)
insert #t(ID,NAME,POSITION)
select 1,'A','aa' union all
select 2,'B','bb' union all
select 3,'C','cc' union all
select 4,'D','dd' union all
select 5,'E','ee' union all
select 6,'F','ff' union all
select 7,'F','ff' union all
select 8,'F','ff' union all
select 9,'B','bb' union all
select 10,'B','bb' union all
select 11,'B','bb' union all
select 12,'A','aa'

-- declare variables as of
-- pseudocode function "TOPMULTIPLEOF(multiplenumber,top)"

declare @multiplenumber int, @top int
select @multiplenumber = 5, @top = 2

/****************
* One Way *
*****************/
declare @ID int, @rowcount int

set @rowcount = ( @multiplenumber * ( @top - 1 ) + 1 )
set rowcount @rowcount
select @ID = ID from #t order by ID

set rowcount @multiplenumber
select ID,NAME,POSITION from #t where ID >= @id order by ID

/****************
* Another Way *
*****************/
set rowcount 0
declare @sql nvarchar(512)
set @sql = '
select a.ID,a.NAME,a.POSITION from
( select top ' + ltrim(@multiplenumber*@top) + ' ID,NAME,POSITION from #t order by id ) a
left join
( select top ' + ltrim(@multiplenumber*(@top-1)) + ' ID from #t order by id ) b
on a.ID = b.ID
where b.ID is null
'
-- print @sql
exec sp_executesql @sql

drop table #t[/code]

rockmoose

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-27 : 05:17:01

thanks rockmoose...

Your first way is much easy to understand because its short. The shorter the qeurry the better.

But as i try to execute the function TOPMULTIPLEOF(5,2)? OK the result is satisfy.
But as i want to querry and show all the record by "SELECT * FROM tblAida"? it gives
me the result of the function TOPMULTIPLEOF I recently executed and that confuses us.

lol

It was later that i found out that you forgot to reset the rowcount to zero on the last part of the querry. By the way thanks.

Do mo Arigato Guisaimasu.

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources