Determining Top # from MySQL

------=_NextPart_000_0038_01CAD8E8.C0F5D8B0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit



Given a MySQL query like this $q = "select num from table", I get a result
like this:



+---+

|num|

+---+

| 1|

| 4|

| 6|

| 2|

| 4|

| 5|

| 3|

| 2|

| 4|

| 2|

| 3|

| 3|

| 2|

| 1|

+---+



What I want is a listing of numbers sorted by the amount of times they
appear (so I can take a top 5, or top 10):



+---+-----+

|num|count|

+---+-----+

| 2| 4|

| 3| 3|

| 4| 3|

| 1| 2|

| 5| 1|

| 6| 1|

+---+-----+



Is this a query that I can feed to MySQL, or is this something I need to
sort out in PHP?


------=_NextPart_000_0038_01CAD8E8.C0F5D8B0--

Re: Determining Top # from MySQL

Ashley M. Kirchner wrote:
>
> Given a MySQL query like this $q = "select num from table", I get a result
> like this:
>
> +---+
> |num|
> +---+
>
> | 1|
>
> | 4|
>
> | 6|
>
> | 2|
>
> | 4|
>
> | 5|
>
> | 3|
>
> | 2|
>
> | 4|
>
> | 2|
>
> | 3|
>
> | 3|
>
> | 2|
>
> | 1|
>
> +---+

>
> What I want is a listing of numbers sorted by the amount of times they
> appear (so I can take a top 5, or top 10):
>
> +---+-----+
> |num|count|
> +---+-----+
>
> | 2| 4|
>
> | 3| 3|
>
> | 4| 3|
>
> | 1| 2|
>
> | 5| 1|
>
> | 6| 1|
>
> +---+-----+
>
> Is this a query that I can feed to MySQL, or is this something I need to
> sort out in PHP?


Dunno, is this relevant? http://lists.mysql.com/mysql/25829

kdk


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Determining Top # from MySQL

--0016361e7f34aa4d190483ece7b1
Content-Type: text/plain; charset=ISO-8859-1

Eh, he's off topic, but we've talked plenty SQL on here before...

SELECT num, COUNT( num ) FROM table GROUP BY num;

I don't have a myqsl server to test that, but should do it.

--
-Dan Joseph

www.canishosting.com - Unlimited Hosting Plans start @ $3.95/month. Promo
Code "NEWTHINGS" for 10% off initial order

http://www.facebook.com/canishosting
http://www.facebook.com/originalpoetry

--0016361e7f34aa4d190483ece7b1--

Re: Determining Top # from MySQL

--000e0cd72a9ad376a70483ef3280
Content-Type: text/plain; charset=UTF-8

If you want to take top 5:
SELECT num, COUNT( num ) as c FROM table GROUP BY num order by c desc limit
5


Thanks,
Vikash Kumar
--
http://vika.sh


On Sun, Apr 11, 2010 at 8:07 AM, Dan Joseph wrote:

> Eh, he's off topic, but we've talked plenty SQL on here before...
>
> SELECT num, COUNT( num ) FROM table GROUP BY num;
>
> I don't have a myqsl server to test that, but should do it.
>
> --
> -Dan Joseph
>
> www.canishosting.com - Unlimited Hosting Plans start @ $3.95/month. Promo
> Code "NEWTHINGS" for 10% off initial order
>
> http://www.facebook.com/canishosting
> http://www.facebook.com/originalpoetry
>

--000e0cd72a9ad376a70483ef3280--

Re: Determining Top # from MySQL

On Apr 10, 2010, at 10:02 PM, Ashley M. Kirchner wrote:

>
>
> Given a MySQL query like this $q = "select num from table", I get a result
> like this:
>
>
>
> +---+
>
> |num|
>
> +---+
>
> | 1|
>
> | 4|
>
> | 6|
>
> | 2|
>
> | 4|
>
> | 5|
>
> | 3|
>
> | 2|
>
> | 4|
>
> | 2|
>
> | 3|
>
> | 3|
>
> | 2|
>
> | 1|
>
> +---+
>
>
>
> What I want is a listing of numbers sorted by the amount of times they
> appear (so I can take a top 5, or top 10):
>
>
>
> +---+-----+
>
> |num|count|
>
> +---+-----+
>
> | 2| 4|
>
> | 3| 3|
>
> | 4| 3|
>
> | 1| 2|
>
> | 5| 1|
>
> | 6| 1|
>
> +---+-----+
>
>
>
> Is this a query that I can feed to MySQL, or is this something I need to
> sort out in PHP?
>

This query should do it for you:
SELECT
num,
COUNT(num) AS total
FROM
table
GROUP BY
num
ORDER BY
COUNT(num) DESC
LIMIT 10

Take care,
Floyd


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php