SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Top X records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chrisk
Starting Member

United Kingdom
3 Posts

Posted - 12/18/2012 :  04:48:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 12/18/2012 :  05:05:48  Show Profile  Reply with Quote
>> 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

United Kingdom
3 Posts

Posted - 12/18/2012 :  06:11:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 12/18/2012 :  06:26:23  Show Profile  Reply with Quote
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

United Kingdom
3 Posts

Posted - 12/18/2012 :  08:39:03  Show Profile  Reply with Quote
Perfect :)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/24/2012 :  06:26:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000