[sqlite] Sorting by greatest count amount

[Reinhard Max]

On Thu, 6 Aug 2015 at 17:51, Reinhard Max wrote:

> On Thu, 6 Aug 2015 at 17:41, jose isaias cabrera wrote:
>
>> SELECT pmuk, count(*) FROM LSOpenJobs WHERE pmuk LIKE '% %' GROUP BY pmuk;
>
> For ordered results you need an ORDER BY clause, and if you want to 
> order by a calculated column (count in this case), you have to give 
> it a name:
>
> SELECT pmuk, count(*) AS amount
> FROM LSOpenJobs
> WHERE pmuk LIKE '% %' GROUP BY pmuk
> ORDER BY amount

BTW, an alternative to naming the column is repeating the expression 
that was used to calculate it:

SELECT pmuk, count(*)
FROM LSOpenJobs
WHERE pmuk LIKE '% %' GROUP BY pmuk
ORDER BY count(*)

You can even use the expression for sorting without including the the 
value in the result set:

SELECT pmuk
FROM LSOpenJobs
WHERE pmuk LIKE '% %' GROUP BY pmuk
ORDER BY count(*)

cu
        Reinhard