in reply to More MySQL woes

You get, for each user name, exactly the max(date), and the corresponding row. Maybe you want to work on your WHERE clause - maybe you wanted to select all rows that have date = max(date), but you don't tell us. Also, this is really in SQL-land, not in Perl land. In Perl, I would use grep.

Replies are listed 'Best First'.
Re^2: More MySQL woes
by cosmicperl (Chaplain) on Jan 20, 2008 at 20:10 UTC
    Unforunately mysql doesn't allow WHERE date = MAX( date ), very frustrating
[reply]
      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

      [download]
      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]
      Perhaps an alias can help you (not tested): 
      SELECT max(date) as m, ... WHERE date = m;

      [download]
[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]

In Section Seekers of Perl Wisdom