how to get top 10 and least 10 records with total

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 2008 Forums

Transact-SQL (2008)

how to get top 10 and least 10 records with total

Author

Topic

prasad0482
Starting Member

12 Posts

Posted - 2010-09-17 : 08:16:41

Hi good day,
This is prasad.

How to find Top 10 and Least 10 records with Total

select
a.cleintNo
a.ClientName
b.amount

from client_detail a
inner join order b
on a.clientNo=b.ClientNo
select SUM (amount) AS GrandTotal FROM Client_detail

********************************************************
how to get top 10 and least 10 records using total

Thanks in advance.

Prasad.Bonthu

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-17 : 08:29:56

order it by that column and pick top 10.

prasad0482
Starting Member

12 Posts

Posted - 2010-09-17 : 08:41:54

quote:
Originally posted by sakets_2000

order it by that column and pick top 10.

then least 1o how to get

i need it in single query, and it should show in results 20 records

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-17 : 08:44:03

order by column asc, and picking top 10 will give you least 10.
order by column desc, and picking top 10 will give you greatest 10.

parody
Posting Yak Master

111 Posts

Posted - 2010-09-20 : 08:45:44

and union all to get one result set...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-20 : 12:25:22

deleted

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-21 : 08:24:39

YOu can't do select from order by union select from order by. The following doesn't solve your problem, as you provided no sample data, but you can adapt it.
Hint: You can do this ,rank()over(order by sum(number) asc)

declare @table table (col1 int identity(1,1),Number int)

insert into @table
select checksum(newid())
from master..spt_values where type = 'P' and number between 1 and 1000

SELECT Position,number
FROM
(
select 'Top10' as Position,number
,rank()over(order by number desc) as Rank
from @table
union
select 'Bottom10' ,number
,rank()over(order by number asc)
from @table
) a
where rank between 1 and 10

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources