Top 25 and then.... - 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 25 and then....

Author

Topic

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-01-04 : 05:10:33

Hi, I want create a query that query's a table with, lets say, 1000 records. This is the structure:

id - int
Name - varchar(50)
Total - int

example:

id | Name | total
--------------------
1. | piet | 30
2. | henk | 11
3. | dirk | 43
...

I want to display the top 25 ordered by total. This ain't hard.

SELECT TOP 25 id, name, total from testtable order by Total desc

But I also want to know the remaining records, and there total, summed.

So I want to display at the bottom of the query "remaining | 39422" or something like that. Is this possible?

Thank you.

Bjorn

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-04 : 11:07:59

Hi Bjorn,

It's Saturday, the heavyweights must be sleeping late today

so I'll take a crack at your question. (Plus, it doesn't look too difficult).

You're right about the top 25..

SELECT TOP 25 id, name, total from testtable order by Total desc

Now for the rest, select those not IN that set above..

SELECT id, name, total from testtable
WHERE id NOT IN (SELECT Top 25 id from testtable order by Total desc)
COMPUTE SUM(total) AS GrandTotal

I would like to see someone post the solution using EXISTS instead of IN as I have. I understand EXISTS is generally regarded as a better solution.

There's another way -- You could join two tables and select the 'others' as those with NULL in the joined table. Does that make sense?

SELECT id, name, total from testtable T1
left outer join (select top 25 id from testtable order by Total desc) T2
ON T1.id = T2.id
WHERE T2.ID IS NULL
COMPUTE SUM(total) AS GrandTotal

There ! Two solutions to debug. (I haven't tried either.)

Good luck,

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-04 : 15:11:24

Or maybe:

Select b.id, isnull(b.name, 'Remaining'), sum(a.total)
FROM
Table a
LEFT OUTER JOIN
(SELECT TOP 25 * from Table ORDER BY Total) B
ON
a.id = b.id
GROUP BY b.id, b.name

Kind of backwards, grouping by the outer table of the LEFT OUTER JOIN, but that should work and it will give you one record with the grand total of the non top 25 records.

- Jeff

bjornh
Yak Posting Veteran

87 Posts

Posted - 2003-01-08 : 14:06:19

Sorry for the late reaction, but thanks, After some modification i've got it working...

Bjorn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources