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)
 Top X records

Author  Topic 

Chrisk
Starting Member

3 Posts

Posted - 2012-12-18 : 04:48:17
Hi,

I'm trying to select the top X records and order them by the count I am performing. Everything works without "Top X" added but when I add in the "Top X" the rows returned are not the rows with the highest count, it appears to return the first 5 "ResourceMaster_Titles" rather than the top x counts.

select Top 5 resourcemaster_title as Title,
count(pastloan_borrower_id) as Total,
borrower_gender as Gender
from pastloan
inner join resourceitem on resourceitem_id = pastloan_resourceitem_id
inner join resourcemaster on resourcemaster_id = resourceitem_resourcemaster_id
inner join borrower on borrower_id = pastloan_borrower_id
inner join tutorgroup on tutorgroup_id = borrower_tutorgroup_id
where resourcemaster_type in (0,13,16)
and pastloan_recycled IS NULL
and borrower_recycled is null
and tutorgroup_recycled is null
AND convert(char(8), [PastLoan_RenewalTime], 108) = '00:00:00'
and borrower_type = 0
and borrower_gender = 1

group by borrower_gender,resourcemaster_title


union all

select Top 5 resourcemaster_title as Title,
count(pastloan_borrower_id) as Total,
borrower_gender as Gender
from pastloan
inner join resourceitem on resourceitem_id = pastloan_resourceitem_id
inner join resourcemaster on resourcemaster_id = resourceitem_resourcemaster_id
inner join borrower on borrower_id = pastloan_borrower_id
inner join tutorgroup on tutorgroup_id = borrower_tutorgroup_id
where resourcemaster_type in (0,13,16)
and pastloan_recycled IS NULL
and borrower_recycled is null
and tutorgroup_recycled is null
AND convert(char(8), [PastLoan_RenewalTime], 108) = '00:00:00'
and borrower_type = 0
and borrower_gender = 2

group by borrower_gender,resourcemaster_title

order by borrower_Gender, total desc

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-18 : 05:05:48
>> order by borrower_Gender, total desc

First it will sorts based on Gender, then on Total

Try this order by total desc, borrower_Gender



--
Chandu
Go to Top of Page

Chrisk
Starting Member

3 Posts

Posted - 2012-12-18 : 06:11:52
Thanks for the reply,

I changed the order but got the same results but they weren't split into Gender.

It seems to select the first Titles then order by the gender then count.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-18 : 06:26:23
SELECT TOP 5 *
FROM ( SELECT titles, total ....
UNION ALL
SELECT titles, total ....
) t
ORDER BY total DESC


--
Chandu
Go to Top of Page

Chrisk
Starting Member

3 Posts

Posted - 2012-12-18 : 08:39:03
Perfect :)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-12-24 : 06:26:40
Also refer http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -