Select statement returning incorrect row

Dixen

This is my current SELECT statement:

SELECT max(date),countryname,countrynum,troops FROM ".$prefix."_statusuploads 
GROUP BY countryname ORDER BY troops DESC LIMIT 10

What I am trying to do is build a table of the Top 10 Countries in the Troop category. Each player uploads a status page that lists their various military counts, which have a unix timestamp in the 'date' field. Each country should only be able to show up on the list once...

Currently my SELECT is only returning the value of the FIRST uploaded status report. I would iimagine this could be a simple fix if I could get it to "ORDER BY troops DESC" before the group by function.

Any help would be appreciated.

Thanks.

psycorpse

I think your problem might be with your max(date). I think that is limiting you to 1 row. Try using just date and see if that pulls all 10 rows.

Dixen

The 10 rows that I am looking for are 1 for each of the top 10 countries... the limit on max(date) is making sure that only 1 row per country (supposedly the one with the highest value in "date") will be returned.

Dixen

By playing around a bit I have altered my SELECT to this:

SELECT DISTINCT countryname,countrynum,troops FROM ".$prefix."_statusuploads ORDER BY troops DESC LIMIT 10

The first query returned 10 seperate country records, just not necessarily the last one entered for that country...

The second query returns the correct enteries for highest value of 'troops'... but allows a country to be repeated (showing multiple records of the same country).

How can I fix this?

Dixen

Okay... update time. This will be good for a laugh I think...

This is the "current" SELECT statement:

SELECT max(date),countryname,countrynum,max(tanks)tanks FROM ".$prefix."_statusuploads 
GROUP BY countryname,countrynum ORDER BY tanks DESC LIMIT 10

Now... I look at that and see the 'max(tanks)tanks' and know that it is WRONG. That first happened as a typo when I was trying a few different things.

Currently, it "works"... whatever that means though. If I make it 'max(tanks), tanks' or take the 'tanks' part out the query stops working... but ATM it works as I want it to based on a hand check of the results verses what I know is in the table in question.

nemonoman

What your new statement will return is latest update time and the highest number of tanks by countryname. If a country has LOST tanks recently, that won't be returned...just the HIGHEST NUMBER of tanks the country ever had.

Dixen

Is there any way to make this work then?

nemonoman

I've been wondering the same thing myself, and have tried a number of experiments. As near as I can tell, there is no direct way to select both the max(date) and the associated number of troops, tanks, etc. in the record having the max(date) in one pass.

Do it in two queries is easy:

SELECT max(date), countryid
FROM sometable
GROUP BY countryid
LIMIT 10

use the returned rows to select the relevant record:

SELECT *
FROM sometable
WHERE date=$maxdate
AND countryid=$countryid

It sucks, but I think that's the situation.

I can't even see how to do this with a subselect

IF YOU changed your structure to support an autoincrementing id column, you could select those IDs grouped by pubid in a subselect.

Dixen

So basicly we are talking a query, followed by a foreach select based on results of the first query.

All I have to say is YUCK!

mySQL really needs some sort of selectrow func... then you could selectrow based on max(date) grouped by whateverthehell you like, then run with it...

When I get the whole thing worked out (later tonight or tomorrow morning when I have time) I will post what I come up with for future reference...

I never did find anything that really helped in my search of the fora. 🙁

Dixen

Okay... I've now stared at this thing for another 4 and half hours. I have it worked down to "simply" sorting the array I have built to get the results I want.

Here's what I got so far... it's kinda long, and basicly ugly, and IF I can get the damn thing to sort it'll work... I'm not sure I care about anything else right now. grins

So, if anyone can help out with the sorting of the array issue I would appreciate it... I'm going to edit the title of the thread to reflect the direction change of help needed.

curses lousy sort.

    $alphanum = $db->sql_numrows($alpharesult = $db->sql_query("SELECT countryname,countrynum,max(date) 
AS date FROM ".$prefix."_".$table." 
GROUP BY countryname,countrynum"));
    $alpharow = mysql_fetch_array($alpharesult);
    $a = 0;
    while ($a < $alphanum) {
     $alphalist[$a]['countryname'] = mysql_result($alpharesult,$a,"countryname");
     $alphalist[$a]['countrynum'] = mysql_result($alpharesult,$a,"countrynum");
     $alphalist[$a]['date'] = mysql_result($alpharesult,$a,"date");
     $a++;
    }
    $b = 0;
    while ($b < $alphanum) {
     $countryname = $alphalist[$b]['countryname'];
     $countrynum = $alphalist[$b]['countrynum'];
     $date = $alphalist[$b]['date'];
     $betaresult = $db->sql_query("SELECT * FROM ".$prefix."_".$table." WHERE date=$date");
     $alphalist[$b]['troops'] = mysql_result($betaresult,0,"troops");
     $b++;
    }

// this little piece of code is from 
[url]http://www.php.net/manual/en/function.array-multisort.php[/url]  I was having trouble with my sort and thought it might help.
    foreach ($alphalist as $key => $row) {
     $countryname[$key] = $row["countryname"];
     $countrynum[$key] = $row["countrynum"];
     $date[$key] = $row["date"];
     $troops[$key] = $row["troops"];
    }
    array_multisort($troops, SORT_DESC);

Thoughts, comments, sarcastic remarks?

EDIT: Apparently you cannot edit the main title of a thread after creation... grrr!