[Resolved] To display ranking based on total in another Column

kcgame

Hi,

Running into problems again...🙁 This time round, i want to show user a list of ranking of Realms. Please allow me to explain further,
I have the following simplified table,

id | RealmName| Realm| NW |
1 | Hello_there|20010 | 1000

2 | No_Hello_s |99999| 1010
3 | Hello_there |20010| 900

I would like to rank the realms from the largest total NW to lowest total NW like:

rk | RealmName| Realm| NW |
1 | Hello_there|20010 | 1900

2 | No_Hello_s |99999| 1010

I can do this by writing to an array first then output to a table (tedious:bemused: ) but i believe there's definately a way to do this by MySQL Query statement. I'm using MySQL 4.1.7 and PHP 4.3.9
Any help / Comment is greatly appreciated. Thanks🙂

Drakla

SELECT realmname, SUM(NW) as rank FROM table ORDER BY rank GROUP BY realm

EDIT: probably you want to order by rank desc to show the top ones first

kcgame

Thanks, Drakla.
Will try it out when i get home, but i think i don't need "GROUP BY realm" because my end result only consists of distinct Realm, for example, there'll be only one 20010 and 99999 in my final table (cause i only need to show the total NW in each Realm).

Will update here again if i encountered any problems.

liquorvicar

if your Realms are distinct in your table then just:

SELECT realmname FROM table ORDER BY NW desc

will do the job for you.

kcgame

Originally posted by liquorvicar
if your Realms are distinct in your table then just:
SELECT realmname FROM table ORDER BY NW desc
will do the job for you. [/B]

Think this will still give me three ouputs, what it supposed to show is after adding up the total NW in each distinct Realm, it displays from largest NW to Smallest NW.

liquorvicar

in that case you need the GROUP BY in Drakla's original post.
my post said if you have distinct realms in the table (i.e. one entry per realm ID). if you have more than one entry per realm, then you need the GROUP by clause to tell MySQL how to sum the columns.

kcgame

The error🙁 :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY RealmID' at line 1

My code:

$result=mysql_query("SELECT Rname, SUM(NW) as rank FROM users ORDER BY rank DESC GROUP BY RealmID",$link ) or die(mysql_error());

Rname is my column that contains the realm names.
NW is the amount of NW in each realm
RealmID is the realm number ("realm" in the example)

Maybe its my above example confusing everyone, let me simplify my problem:

My table currently has;

id | Realm| NW |
1 | 00001 | 1000
2 | 00002 | 1010
3 | 00001 | 900

I want to display the following after adding up the total NW in each distinct Realm, it displays from total largest to Smallest NW. like;

rk| Realm| NW |
1 | 00001 | 1900
2 | 00002 | 1010

Where rk is rank.
Think this is the most complicated query statement that i've encountered so far...Any help / comments is greatly appreciated. Thanks.

liquorvicar

try this

    $result=mysql_query("SELECT Rname, SUM(NW) as rank FROM users GROUP BY RealmID ORDER BY rank DESC",$link ) or die(mysql_error());

i didn't think the order of GROUP and ORDER BY was important as Drakla had them the other way round but it seems that it may be important.

Drakla

Hi guys - ooops, always getting them the wrong way around, sorry about the blunder.

It seems that you could just as easily group on Rname. I thought from the first post that the realm for an Rname always stayed the same, but if this isn't the case then you may well have to change the grouped item.

kcgame

Originally posted by liquorvicar
try this
    $result=mysql_query("SELECT Rname, SUM(NW) as rank FROM users GROUP BY RealmID ORDER BY rank DESC",$link ) or die(mysql_error());
i didn't think the order of GROUP and ORDER BY was important as Drakla had them the other way round but it seems that it may be important. [/B]

It works😃
Thanks Drakla and liquorvicar for the valuable contributions🙂

😃 Case Closed😃