Finding top -n customers - 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)

Finding top -n customers

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-24 : 04:21:32

Hello again,
I'm trying to find the top 1000 spenders in a table of customers. I can total their spending, but I don't know how to find the top 1000 which will then be appended to a new table. In JET SQL there is a function called TOP to do the job, but this doesn't appear to exist for interbase or sql server.
Does anyone know how to do this?

Cheers

Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-24 : 04:41:05

It does exist in sql server (from v7)

select top 1000 name
from customers
order by spend desc

prior to this

set rowcount 1000
select name
from customers
order by spend desc
set rowcount 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-24 : 05:38:03

Hi,
I'm using interbase and I don't think rowcount exists.
Is there another way to do this?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-24 : 06:16:52

select name
from Customers c, (select c.Spend, seq = (select count(*) from Customners c2 where c2.Spend <= c1.Spend
from customers c1) as a
where c.Spend = a.Spend
and a.seq <= 1000

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources