Quite new to SQL, but trying to make some progress for a baseball-related site of mine.
For instance from my tables I want to produce a query that shows the career leaders for a given stat (Games Played in this case) for the team that I specify.
PHP Code:
$query = "SELECT Pos, FName, LName, SUM(G) FROM retired_batters WHERE Team = 'ATL' GROUP BY LName";
$result = mysql_query($query) or die(mysql_error());
When I run this I get the info that I'm looking for: Position he played, First name, Last Name, Games he played for ATL (and it's obviously sorted by last name.
However, I want this data to be sorted by the sum (i.e. I want to know the top 25 for this stat) but when I try to GROUP BY or ORDER BY I do something wrong and get weird results.
What am I doing wrong and how could I limit the results to only the top 25?
OldPedant would know for sure offhand, but I'm pretty sure MySQL allows you to order by an alias (I know I've tested this time and again but I can never remember which DBMS does what :P). So its very easy:
Code:
SELECT Pos, FName, LName, SUM(G) AS SumOfG FROM retired_batters WHERE Team = 'ATL' GROUP BY LName ORDER BY SumOfG DESC LIMIT 25
Looks like it would work. Ordering by SUM(G) should work as well (if you can't use an alias, but I'm pretty sure MySQL does).
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
Technically, you should *ALWAYS* make sure you GROUP BY *ALL* fields in the SELECT list that are not aggregate functions.
As Fou-Lu gave this:
Code:
SELECT Pos, FName, LName, SUM(G) AS SumOfG
FROM retired_batters
WHERE Team = 'ATL'
GROUP BY LName
ORDER BY SumOfG DESC
LIMIT 25
If you had, for example, 3 different players with the last name of "JONES", then all three would be LUMPED TOGETHER because the GROUP BY specified *only* LName !!
Most database won't even allow the query as written there. MySQL does, but then it has funky rules about what using an incomplete GROUP BY means.
So...
Code:
SELECT Pos, FName, LName, SUM(G) AS SumOfG
FROM retired_batters
WHERE Team = 'ATL'
GROUP BY Pos, FName, LName
ORDER BY SumOfG DESC
LIMIT 25
Note how the SELECTed and GROUPed fields match.
By the by, you'd still be in trouble if you had two players who played the same position and had the same first and last names. Unsual, but not impossible.
p.s.: *ALL* DBs that I know of allow aliased column names in the ORDER BY clause. MySQL is unique in that it also allows them in the GROUP BY clause.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
I've been doing *far* to much work in (sadly) Access O.o
Using the builders I kinda forget about the group by on all fields. Access for sure doesn't let you group by an alias.
*sigh* I wish I could put my scheduling in SQLServer.
I think oracle still rejects groupby on alias? Of course you can ways get around it by selecting a subquery, but its not quite the same. The newer ones may (I use 9g with veeerrrryyyy little actual manual queries; I wrote the sp's awhile ago, and none of them are aggregate).
Edit:
Ah yes sorry I see what you mean. I'm probably thinking of the group by, not the order by. It makes sense to be able to order by any field regardless of the alias.
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
The $i is the important one there. You can format it as you see fit, probably using a table or some nice spans, but that's just a basic printf output.
Edit:
Sorry I missed a part from both the format and the argument list for the position.
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
Oh don't use the if/else like that. It looks like too much code. It can be much simpler. To answer your question, it would go in where you have RANKNUMBER. You can't use RANKNUMBER here since that would represent a constant in php (ie: unchangeable), and failing that it would become a string (which I hate that PHP does that). Effectively, its the $colour number, but instead you would keep going (I'll show you how to do that in one variable):
You should be able to apply your class to the table's tr css, and cascade it to the td from there.
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
if (0 == ($i++ & 1))
{
// this is odd
$sClass = 'odd';
} ...
works.
Because the test is actually done as
Code:
0 == ($i & 1 )
and then, after the text, $i is incremented.
So when $i is even, that expression returns true. But because of the POST-INCREMENT, then by the time we get to the assignment to $class, indeed $i is odd.
So it works.
But it's clear as mud.
I *MUCH* prefer:
Code:
if (0 != (++$i & 1))
{
// this is odd
$sClass = 'odd';
}...
Because here it is clear we are FIRST bumping the value of $i and then testing that new value and, indeed, that new value agrees with the even/odd sense we get.
Of course, I really prefer it all as a ternary operation, as shown in my post, but even when not a ternary operator I think the PRE-INCREMENT makes the operation clearer.
Or do you not think so?
I also think it's a tiny bit clearer to do
Code:
$class = ( ++$i % 2 == 0 ) ? "even" : "odd";
because most people understand the concept of modulo better than bitwise ANDing. But I'll grant you that probably save 2 nanoseconds using the bitwise and.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Last edited by Old Pedant; 01-18-2013 at 01:19 AM..
To me, both of those are simply the same. It doesn't make a difference between the post or the pre operation on it. I see your point though, some people will read it easier seeing that $i is 1 and then checked versus $i is 0, checked and then incremented.
Even clearer though would be to simply not use the increment (either pre nor post) during the if or ternary, and apply it instead to the $i directly when displaying.
PHP Code:
// I'll keep it as the if:
if (0 == ($i & 1))
{
// even
}
else
{
// odd
}
// later
print ++$i;
Ternary would be my approach as well; however, I typically limit the ternaries when I can while posting examples simply due to PHP's ltr evaluation of them. Its only a problem when nesting them, but it really is a pain:
Results in mouse, while using a language with rtl:
PHP Code:
int main(int argc, char **argv)
{
int i = 1;
printf("%s",
i == 1 ? "cat" :
i == 2 ? "dog" :
i == 3 ? "mouse" :
"Unknown");
return 0;
}
Results in (what I consider to be correct) 'cat'. I remember a thread not all that long ago for PHP that actually had an issue in the ternary of just a single level in the echo, which was caused by the concatenation and ternary together. But I can't quite recall what it was exactly.
__________________
As of PHP 5.5, the MySQL library has been officially deprecated. It is recommended to move to either MySQLi or PDO libraries for your mysql connectivity. See here for help choosing which interface you prefer: http://php.net/manual/en/mysqlinfo.api.choosing.php
And ONLY PHP would return a row if fetch_assoc can get one but false if it can't.
UGH UGH UGH.
Any self respecting language would return null instead of false, since an object reference being null makes sense, but a reference being false just cries of inconsistency.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Before you post, read our: 
