Rank by 2 columns in mysql

[vegetable21]

Hi All,

I'll try and explain as best as possible what I'm trying to do and hopefully someone can tell me the best way to do it!

I'm basically trying to rank by 2 columns in mysql/php, by this i mean i have a column called `Turnover` and one of `Cash` I would like to display each column in a table with its ranking in the column to the right.

So far i have:

include ("database.php");
global $conn;

mysql_query("set @rankA=0;"); 
$sql = "
	SELECT `name`, `id`, `year`, `Turnover`, `Cash`, @rankA:=@rankA+1 AS rankA 
	FROM stocks
	LEFT JOIN data ON `id` = `company_id` 
	WHERE 	`year` = '2007' AND
			`Turnover` IS NOT NULL AND
			`Turnover` != 0
	ORDER BY Turnover DESC
;";

	$result = mysql_query($sql);
	echo mysql_num_rows($result)."<br />";
	echo $sql."<br />";
	echo "<table width=200 border=1>\n";
	while($row = mysql_fetch_assoc($result)) {
		echo "<tr>\n";
		echo "\t<td>".$row['id']."</td>\n";
		echo "\t<td>".$row['name']."</td>\n";
		echo "\t<td>".$row['year']."</td>\n";
		echo "\t<td>".$row['Turnover']."</td>\n";
		echo "\t<td>".$row['rankA']."</td>\n";
		echo "\t<td>".$row['Cash']."</td>\n";
		echo "</tr>\n";
	}
	echo "</table>\n";
	mysql_close($link);

Now obviously this works very well and shows me the data ordered by Turnover with rankA in the column to the right, but what i really want to add next is to have another column with the value of the data ordered by Cash (rankB). But i of course want to keep rankA as well.

I'm not new to mysql but i'm fully aware that i don't know how to use it to its greatest potential. The only solution that i have at the moment is to have a whole seperate php function which computes rank for Turnover, and stores it in the table, then computes rank for Cash and stores that in the table too. Now with the amount of data i have this is really not an option, plus the data is frequently updated so the function would have to be rerun every time.

I'm thinking there must be a way to run the query ordered by Turnover and then somehow carry rankA through for when i run it again ordered by Cash, setting RankB; then display the whole lot, but i simply don't know how.

Any suggestions would be much appreciated.

Thanks

Andy

[chaarmann]

Why don't you just get the data unsorted from the database and store them in an array? Then to get the data sorted the one or the other way is just a single command in PHP that sorts the whole array by the column given.

It is generally a bad idea to put a lot of load on the database to let it sort the records. You are slowing down every other application that also needs data from the database. If you sort it yourself, you only make your own process slower but don't affect others.

[vegetable21]

Ah I c, that would explain a lot! many thanks for pointing that out!

However I now have another problem in that I can't sort the array in php properly.

bool array_multisort ( array $arr [, mixed $arg [, mixed $... ]] )

For Example:

$sql = "SELECT `name`, `company_id`, `year`, `Turnover` FROM `data` WHERE `year` = '2007';";
$result = mysql_query($sql, $conn);
if (!$result) { die("Invalid query: " . mysql_error()); }
$arr = array();
while($row = mysql_fetch_assoc($result)) {
	$arr[] = $row;
}
array_multisort($arr['Turnover'], SORT_NUMERIC, SORT_DESC);

Surely the above is sorting each $row array within $arr rather than sorting by $arr['Turnover'] within the whole $arr?

I've read http://uk.php.net/array_multisort and the w3schools pages on array sort but I'm still not getting it,

And help would be much appreciated.

[wizardry]

you could use order by for multiple columns and adjust the where clause for that fields.

Usually your sla will have more designated cpu time for your database then app. or equally spread just depends.

but you could have the right sla where you dont need to worry about over head.

either way its better to have the database do the work from a dba point of view.

[vegetable21]

wizardry, that's what I had originally thought that it would be better to let the database do the ranking but then the time delay started creeping in.

I think I would like to know how to implement both solutions that wizardry and chaarmann have eluded to,

1) if someone could advise me the correct way to implement the array sort using my example in my last post, I would be eternally grateful.

&

2) if someone could show me how order by multiple columns then that would be great too, but please see my original post and note that I simply don't want to order by one then the other. I have no idea if this is possible, but wizardry is suggesting it is.

[chaarmann]

Surely the above is sorting each $row array within $arr rather than sorting by $arr['Turnover'] within the whole $arr?

I never used arrays in php before, but it seems to be not logical to me how you try to sort it . All sort functions I have seen so far, no matter what language, need an index and the array itself. But $arr['Turnover'] only gives you the index, so where is the array itself in your argument list that you pass to your sort function ???
I googled an found this:

static function multi2dSortAsc( &$arr, $key){
$sort_col = array();
foreach ($arr as $sub) $sort_col[] = $sub[$key];
array_multisort ($sort_col, $arr);
}
The crux is that we have to create the 1D array we want to sort by on the fly, once we have this array it can be used to sort the parent 2D array by passing it as a second argument.

As I said, I am a newbie in php, but how I understand it is that you did not pass the array as second argument. Just add it in your original code:

array_multisort($arr['Turnover'], $arr, SORT_NUMERIC, SORT_DESC);

I have no possibility to run PHP code myself, so if that doesn't work, then you should ask this question about sorting in the PHP forum again.

If you need the array sorted by different columns (I assume you have a column name in your GUI or webpage that you can click or select to sort it), then get it unsorted from database, buffer it and sort it in php. In this case you only access the database once and get the data, no matter how often the user wants it to sort. That's a huge performance gain than doing the opposite: get it again and again from database everytime the user clicks on a column to sort it.
And what about different character sets and collations your DB uses while sorting? How does your database suport that? I guess with proprietary SQL-syntax only. And if you change the database? Then you need to rewrite all your SQLs! In opposite to that the php-code for sorting an array always stays the same.

Sorting by php puts load on the webserver, sorting by DB puts load on the database server. What if you run into performance problems? Then in most cases it's much easier to just add a new webserver than to add a new database server and do all the required data synchronization s between both DB-servers.

Anyway, there is only one situation where you should let the database sort the results:
If for example the query returns million of records, but you only need the first 10 of them (SQL-command "limit"). Then it's a lot of faster that way. But in your query provided you don't use.
if you still want to let the database sort it then you could just modify the "ORDER BY Turnover DESC" clause in your SQL-query by replacing "Turnover" with the column name you want to sort by.

[vegetable21]

thanks chaarmann, i found that link not long after i posted the last response.

i have now got it doing exactly what i want using the array_multisort (), the documentation on php.net is pretty rubbish on this but yes you are right you need an index to sort the array against. it's still quite slow, but not half as slow when i was getting SQL to do the sorting, and when i consider the amount i'm trying to make it do its pretty fast.

many thanks for all contributions!