That's because in a GROUP BY query that sort of restriction has to go in a HAVING clause. What you're doing is called "group-wise maximum" and is a common problem with SQL. There's a page with several good solutions in the MySQL docs.
For future MySQL questions, I recommend asking on the MySQL users mailing list. | [reply] |
After more than 3 hours of searching, and now minus a lot of hair, I finally found the solution. I can't believe they've made something that should be so simple, so hard.
SELECT pl.username, amount, pl1.date
FROM paylist pl1
JOIN (
SELECT username, MAX( date ) AS date
FROM paylist pl2
GROUP BY username
) AS pl2 ON pl1.username = pl2.username
AND pl1.date = pl2.date
If you ask me it should match up the rows by default. Either way, hopefully this'll save someone else from a lot of stress | [reply]
[d/l] |
If you ask me it should match up the rows by default. Either way, hopefully this'll save someone else from a lot of stress
If you mean automatically join tables based on the name of the fields -- Oracle does, and it caused me no end of headaches, because it requires significant planning when designing your tables to ensure that you never use the same field name for more than one purpose. 'id', as the primary key for the table? Nope. 'name' or 'abbr' in tables used in lookup tables? Nope.
It might be useful in same situations, but when the RDBMS starts making incorrect assumptions, it's extremely aggravating.
| [reply] |
You should always use the format
tablename.colname
for statements involving more than one table, otherwise the DB has to guess which one you mean when the tables have the same named column.
In fact, use a table alias for shorter code:
select t1.col1
from table1 t1, table2 t2
where t1.col1 = t2.col1
| [reply]
[d/l] |
Perhaps an alias can help you (not tested):
SELECT max(date) as m, ... WHERE date = m;
| [reply]
[d/l] |
I tried that as well... I finally found a solution below and it's certainly not an obvious one.
Thanks for your help
| [reply] |