SQL Ordering

[HCProfessionals]

I am ordering a table by 2 different functions and am having a lot of trouble

 

ORDER BY Level DESC, Won/(Won+Lost) ASC

[.josh]

well seeing as how you don't really explain what "having a lot of trouble" means, this is just a random guess...it looks like you may really want to be doing something more like this:

 

select Level, (Won/(Won+Lost)) as p from table order by Level desc, p asc

 

 

 

[HCProfessionals]

Still not working...

[HCProfessionals]

ORDER BY Level DESC, Won/(Won+Lost) ASC LIMIT 25"

 

Forgot, I have a limiter on there, would tha have any affect?

[.josh]

how about you show your full query, explain what you are trying to do, and actually explain what isn't working?

[HCProfessionals]

Basically I am trying to sort by Experience (uEXP), then by Win/Loss Percentage.

 

<?php
    $top_column1 .= "<b>Rank</b><br>";
    $top_column2 .= "<b>Username</b><br>";
    $top_column3 .= "<b>Gold</b><br>";
    $top_column4 .= "<b>Army Size</b><br>";
    $top_column5 .= "<b>Level</b><br>";
    $thetopquery = "SELECT uID,uLogin,(uOffensiveMen+uDefensiveMen) AS uArmySize,uGold,uLevel FROM users$whereclause ORDER BY uEXP DESC, uWon/(uWon+uLost) ASC LIMIT 5";
    $topresult = $db->query( $thetopquery );
    while ( $thetopmost = $db->fetch( $topresult ) ) {
        $x++;
        if ($x==1) {
	$top_column1 .= "<hr><img src=\"images/r_1.png\" border=\"0\" title=\"1\" alt=\"1\"><br>";  
	} else if ($x==2) {
	$top_column1 .= "<hr><img src=\"images/r_2.png\" border=\"0\" title=\"2\" alt=\"2\"><br>";  
	} else if ($x==3) {
	$top_column1 .= "<hr><img src=\"images/r_3.png\" border=\"0\" title=\"3\" alt=\"3\"><br>";  
	} else {
        $top_column1 .= "<hr>$x<br>";
        }
        $top_column2 .= "<hr><a href=\"profile.php?id=" . $thetopmost['uID'] . "\">" . $thetopmost['uLogin'] . "</a> ";
        $istoponline = $db->fetch( $db->query( "SELECT uID FROM users_online WHERE uTime>'$mintime15' AND uID='" . $thetopmost['uID'] . "'" ) );
        if ( $istoponline ) {
            $top_column2 .= " [Online]<br>";
        } else {
            $top_column2 .= "<br>";
        } 
        $top_column3 .= "<hr>" . $thetopmost['uGold'] . "<br>";
        $top_column4 .= "<hr>" . $thetopmost['uArmySize'] . "<br>";
        $top_column5 .= "<hr>" . $thetopmost['uLevel'] . "<br>";
    } 

    ?>
    <b><tl>Top 5 Players (<i>Best Win/Loss Ratio</i>)</tl></b><br />
<img alt="" src="images/seperator.gif" /><br />
<table width="100%"  border="0">
  <tr>
	<td class="bodycell4">
	<table width="100%" border="0">
	  <tr>
		<td width="10%" align="center">
		<?=$top_column1?>
		</td>
		<td width="35%">
		<?=$top_column2?>
		</td>
		<td width="25%" align="center">
		<?=$top_column3?>
		</td>
		<td width="20%" align="center">
		<?=$top_column4?>
		</td>
		<td width="10%" align="center">
		<?=$top_column5?>
		</td>
	  </tr>
	</table>
	</td>
  </tr>
</table>

[.josh]

As mentioned,  in general you should be doing something like

 

$thetopquery = "SELECT uID,uLogin,(uOffensiveMen+uDefensiveMen) AS uArmySize,uGold,uLevel, (uWon/(uWon+uLost)) as uWLP FROM users$whereclause ORDER BY uEXP DESC, uWLP ASC LIMIT 5";

 

 

What is $whereclause supposed to be? I mean I can infer what it is *supposed* to be based on the variable name and its position in the query string, but did you echo out $thetopquery to see if it is showing what it is supposed to be doing?

 

 

And, you still haven't answered the question I've asked you multiple times: what is it (not) doing that causes you to say something is wrong?  You say something is wrong but you don't explain what is wrong? Are you seeing no results?  Are you seeing result but not ordered properly? WHAT? If you cannot explain WHAT is wrong - what you want vs. what it is doing, then how do you expect yourself or anyone else to be able to fix it?

[HCProfessionals]

Everything outputs correctly when I echo, just doesn't seem to want to order decimals or something. even did 1+(uWon/(uWon+uLost)) but still doesn't want to sort it properly.

[.josh]

Okay first off, the way you currently have it, it will (sub) sort by your formula, but it will not actually show your Win/Loss Percentage numbers as its own column.  In order to do that, you must include it in the SELECT part of your query, as I have shown.

 

Also, I want to make sure we are on the same page here, as far as how this query is actually sorting your data.  Your query says to first list within descending order by uEXP.  Then sub-sort in ascending order by uWLP (your formula).  So overall, you will see the data sorted by uEXP in descending order.  Then, if there is more than one row in uEXP that has the same value, it will then subsort by uWLP's values.

 

Example:

uEXP uWLP
3    1
3    2
3    3
2    1
2    2
1    1

 

Is this what you are expecting to happen?  If not, then give example data, showing how you want it to be sorted (give example with values of how you want the sorted data set to look like)

[HCProfessionals]

You'll need to register, but you can physically see what's going on:

 

Go to http://gglegends.net/attacktest.php (This is the debugging page for: attack.php). If you take a look at the area under declare war. You then see the sorted row level with level (uEXP) and then their Percentage (uWLP). It sorts the levels just fine, but does not sort uWLP.