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.
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 Genderfrom pastloaninner join resourceitem on resourceitem_id = pastloan_resourceitem_idinner join resourcemaster on resourcemaster_id = resourceitem_resourcemaster_idinner join borrower on borrower_id = pastloan_borrower_idinner join tutorgroup on tutorgroup_id = borrower_tutorgroup_idwhere resourcemaster_type in (0,13,16)and pastloan_recycled IS NULLand borrower_recycled is nulland tutorgroup_recycled is nullAND convert(char(8), [PastLoan_RenewalTime], 108) = '00:00:00'and borrower_type = 0and borrower_gender = 1group by borrower_gender,resourcemaster_titleunion allselect Top 5 resourcemaster_title as Title,count(pastloan_borrower_id) as Total,borrower_gender as Genderfrom pastloaninner join resourceitem on resourceitem_id = pastloan_resourceitem_idinner join resourcemaster on resourcemaster_id = resourceitem_resourcemaster_idinner join borrower on borrower_id = pastloan_borrower_idinner join tutorgroup on tutorgroup_id = borrower_tutorgroup_idwhere resourcemaster_type in (0,13,16)and pastloan_recycled IS NULLand borrower_recycled is nulland tutorgroup_recycled is nullAND convert(char(8), [PastLoan_RenewalTime], 108) = '00:00:00'and borrower_type = 0and borrower_gender = 2group by borrower_gender,resourcemaster_titleorder 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 descFirst it will sorts based on Gender, then on TotalTry this order by total desc, borrower_Gender--Chandu |
|
|
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. |
|
|
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 .... ) tORDER BY total DESC--Chandu |
|
|
Chrisk
Starting Member
3 Posts |
Posted - 2012-12-18 : 08:39:03
|
Perfect :) |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|