Top 20 Results - 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

SQL Server Development (2000)

Top 20 Results

Author

Topic

O11Y
Starting Member

3 Posts

Posted - 2006-09-22 : 08:46:55

Hello,

This is a bit of a long winded question but bear with me! I'm writing a series of SQL Select queries that are to be executed through our company's billing system to produce targetted reports.

What happens is that the query generates a large table of results containing a column called "User". The application runs the query and then emails each each User their specific rows from the result set.

With me so far?

I'm trying to make a report where each user only gets their "Top 20" rows from the result set. For Example; I have a report of "Outstanding Client Balences" and the "User" in this case is the Partner for that client. So if a Partner only has 12 Clients with an outstanding balence they recieve 12 rows, but if a Partner has 40 Clients they get the top 20 Rows (by amount).

The problem being that a simple

"select top 20 * from myTable"

will only get 20 rows for the whole set whereas I need the top 20 BY USER (giving 'Number of Rows' <= 20 * 'No. of Users'). Is there any way to do this? Perhaps as part of a join or group by statement?

Any help would be much appreciated,

Olly

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-22 : 08:51:15

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 2

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 08:51:48

Look here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62021&SearchTerms=top,group,by

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 08:53:21

Peter Larsson
Helsingborg, Sweden

O11Y
Starting Member

3 Posts

Posted - 2006-09-22 : 11:33:10

Cheers for that but I'm still having trouble.

The thing is that my query has a number of joins on very large tables and the "Rank" is calculated from a SUM-ed expression between columns on different tables.

The problem is that the nested query used with the "In" / Having Clause to generate the top 20 is essentially a nested copy of the same query so when you're running this for 200,000 + rows the SQL server really begins to struggle.

I've included some SQL below (though i've replaced the names with some hopefully more meaningful names). In this example I'm trying to get the top 20 gross_debts for each usr_ident

--DEBT OLDER THAN 90 DAYS
-------------------------

select usr_department, usr_ident, usr_name, case_id, sum(ledger.ledger_amount * credit_debit.deb_cred) as gross_debt

from
case_table inner join
ledger on case_table.case_id = ledger.case_id inner join
ledger_code on ledger.ledger_index = ledger_code.ledger_index inner join
credit_debit on ledger_code.entry_type = credit_debit.type_code inner join
usr_table on case_table.case_usr = usr_table.usr_ident

where datediff(day,ledger.transaction_date, getdate()) > 90

group by usr_department, usr_ident, usr_name, case_id

having sum(ledger_amount * deb_cred) <> 0

order by usr_department, sum(ledger_amount * deb_cred) desc

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-22 : 11:59:05

this line will kill your index:
where datediff(day,ledger.transaction_date, getdate()) > 90

change it to:
declare @fromDate datetime
select @fromDate = getdate()-90
select ...
from ...
where ledger.transaction_date > @fromDate

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-22 : 12:01:54

also try it like this:


declare @fromDate datetime
select @fromDate = getdate()-90

select	usr_department, usr_ident, usr_name, case_id, gross_debt
from	
	(
	select usr_department, usr_ident, usr_name, case_id, sum(ledger.ledger_amount * credit_debit.deb_cred) as gross_debt
	from 
		case_table inner join 
		ledger on case_table.case_id = ledger.case_id inner join
		ledger_code on ledger.ledger_index = ledger_code.ledger_index inner join
		credit_debit on ledger_code.entry_type = credit_debit.type_code inner join
		usr_table on case_table.case_usr = usr_table.usr_ident
	where ledger.transaction_date > @fromDate
	group by usr_department, usr_ident, usr_name, case_id
	) t1
where 	gross_debt <> 0
order by usr_department, gross_debt desc

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources