| Author |
 Topic  |
|
|
Chrisk
Starting Member
United Kingdom
3 Posts |
 Posted - 12/18/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/18/2012 : 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 |
 |
|
|
Chrisk
Starting Member
United Kingdom
3 Posts |
Posted - 12/18/2012 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 12/18/2012 : 06:26:23
|
SELECT TOP 5 *
FROM ( SELECT titles, total ....
UNION ALL
SELECT titles, total ....
) t
ORDER BY total DESC
--
Chandu |
|
|
|
Chrisk
Starting Member
United Kingdom
3 Posts |
Posted - 12/18/2012 : 08:39:03
|
| Perfect :) |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
| |
Topic |
|
Top X records
