Sorting by greatest count amount

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Sorting by greatest count amount

jose isaias cabrera

Greetings.

I have this query,

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

this works but it is not sorted.  I tried things like

SELECT pmuk, count(*) FROM LSOpenJobs WHERE pmuk LIKE '% %' GROUP BY count(*) || '-' || pmuk;

and

SELECT pmuk, count(*) FROM LSOpenJobs WHERE pmuk LIKE '% %' GROUP BY '' || count(*) || '' || '-' || pmuk;

I would like to have the pmuk sorted by the lowest to highest, if possible.  Otherwise, the opposite.  Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sorting by greatest count amount

Reinhard Max

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

Does this do what you want?

cu
  Reinhard
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sorting by greatest count amount

jose isaias cabrera
In reply to this post by Reinhard Max

"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
>
> Does this do what you want?

Darn it!  I keep forgetting about ORDER BY.  Yep, that is exactly what I
wanted.  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Sorting by greatest count amount

jose isaias cabrera
In reply to this post by Reinhard Max

"Reinhard Max" wrote...

> 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(*)

Another unexpected lesson today!  Yay! Muchas gracias, or just simply,
danka. :-)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users