cosmicperl has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,
  Ok, I know this isn't really a Perl question. Actually it's not a Perl question at all, although I am using the Perl DBI. I wouldn't normally post a question like this here but this is driving me mad and I need to get a project finished tonight for a deadline.
There is always some clever sod here that knows what to do :)
I've been ripping my hair out trying to get this select to return what I want. Here is what I have so far:-

create table paylist (date int(8), username varchar(12), amount real(12,2))

Some example data:-
INSERT into paylist(date, amount, username) values ('20080101', 10, 'bob'), ('20080102', 20, 'bob'), ('20080103', 5, 'bob'), ('20080102, 0, 'laura')

What I want is to get returned is this:-
20080103, 5, bob
20080102, 0, laura

The real table has lots of different users. Now if I use:-

SELECT MAX( date ) AS m, amount, username
FROM `paylist`
WHERE 1
GROUP BY username
ORDER BY m DESC

[download]
I Get:-
20080103, 10, bob 20080102, 0, laura

Seems no matter what I try, I cannot get the amount that corrosponds with the MAX( date ).

Please someone help me.


Thank you.

Replies are listed 'Best First'.
Re: More MySQL woes
by Corion (Patriarch) on Jan 20, 2008 at 19:56 UTC

    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.

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

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom