hi ..........

Question

hi ..........

praveen.rewadia

Newbie

Points: 4
More actions
May 26, 2009 at 9:36 am

#210107

HI All,
I have a query.
I have to find top 10 records out of hundreds and rest of then is shown in 11th row named as others.
pls guide me .
with regards
Praveen

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

CrazyMan SSChampion Points: 12560 More actions · Answer 1

sorry i cant get your post, are you trying to say that you need to display top 10 records from your table and then the 11th record must follow the 10th one,if this is what you mean then a normal select statement will do the job

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

you need to create a calculated member that is the sum of all of the 'other' (ie not top 10) records. Prob need to use something like EXCLUDE (or is it except?) to eliminate the items that are in the top 10. When you have this calc member, you can then add it to the set of top 10 to give you 11 members, the top 10 individuals plus the aggregated 11th member.

Steve.

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

I think you are talking about something like this. I just used 5 records in the example. Also, what version of SQL are you running?

--Begin Test Data

DECLARE @tbl Table(id varchar(10), amt money)

INSERT INTO @tbl

Select 1, 25 UNION ALL

Select 2, 30 UNION ALL

Select 3, 80 UNION ALL

Select 4, 20 UNION ALL

Select 5, 34 UNION ALL

Select 6, 123 UNION ALL

Select 7, 456 UNION ALL

Select 8, 18

--End Test Data

Select * FROM

(Select top 5 id, amt

FROM

@tbl

Order BY amt DESC) A

UNION ALL

Select 'Others' AS id,

SUM(amt) AS amt

FROM @tbl

WHERE id not in ( Select top 5 id

FROM

@tbl

Order BY amt DESC)

If you are running 2005 or later you can use a CTE.

--Begin Test Data

DECLARE @tbl Table(id varchar(10), amt money)

INSERT INTO @tbl

Select 1, 25 UNION ALL

Select 2, 30 UNION ALL

Select 3, 80 UNION ALL

Select 4, 20 UNION ALL

Select 5, 34 UNION ALL

Select 6, 123 UNION ALL

Select 7, 456 UNION ALL

Select 8, 18

--End Test Data

;WITH CTEID

AS

(

Select top 5 id, amt

FROM

@tbl

Order BY amt DESC

)

Select * from CTEID

UNION ALL

Select 'Others' AS id,

SUM(amt) AS amt

FROM @tbl

WHERE id not in ( Select id FROM CTEID)

praveen.rewadia Newbie Points: 4 More actions · Answer 4

Hi Ken,

ya same as u told me.....this is might be solution of my problem.

Thanks a lot for giving me ur precious suggestion.

with lot of regards..

Praveen Rewadia

praveen.rewadia Newbie Points: 4 More actions · Answer 5

HI thanks for giving time for my problem.

I want to say that i want to show top 10 records nd their aggregate and rest of others records which not included in top 10 shown in 11th row named as 'others'.

might be u got my problem.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Just out of interest, this solution will work great if you're in SQL, but seeing as you've posted to the AS forum, the assumption is you'll be using MDX and AS and you'll find that the TSQL approach won't work.

Steve.