Is there any way of using COUNT(*) in an ORDER BY clause. I often want to group things together and look at the biggest first eg

SELECT SUBSTR(_emSender,POS('@',_emSender)+1,Length(_emSender)), COUNT(*)
FROM SpamStore
WHERE
_emSender IS NOT NULL
GROUP BY SUBSTR(_emSender,POS('@',_emSender)+1,Length(_emSender))
HAVING COUNT(*) > 100

I want to add ORDER BY COUNT(*)

In this case it means I can look at the biggest spamming domains first.

Currently I write to a memory table and add an index.

Roy Lambert

Roy

What works for me is if  count(*) is aliased as part of select and then
order by the aliased column name :

e.g. :
select MyId,count(*) as MyCount from Mytable
group by MyId
Order by MyCount

Raul




On 6/21/2012 9:21 AM, Roy Lambert wrote:
> Is there any way of using COUNT(*) in an ORDER BY clause. I often want to group things together and look at the biggest first eg
>
> SELECT SUBSTR(_emSender,POS('@',_emSender)+1,Length(_emSender)), COUNT(*)
> FROM SpamStore
> WHERE
> _emSender IS NOT NULL
> GROUP BY SUBSTR(_emSender,POS('@',_emSender)+1,Length(_emSender))
> HAVING COUNT(*) > 100
>
> I want to add ORDER BY COUNT(*)
>
> In this case it means I can look at the biggest spamming domains first.
>
> Currently I write to a memory table and add an index.
>
> Roy Lambert
>

Raul


Now you say that I remember asking Tim the same question and getting the same answer. I'm getting to old for this

Roy Lambert