Re^3: More MySQL woes

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

Comment on Re^3: More MySQL woes Download Code

Replies are listed 'Best First'.
Re^4: More MySQL woes by jhourcle (Prior) on Jan 20, 2008 at 21:59 UTC
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]
Re^5: More MySQL woes by chrism01 (Friar) on Jan 21, 2008 at 05:05 UTC
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` [download]	[reply] [d/l]
Re^6: More MySQL woes by dsheroh (Monsignor) on Jan 21, 2008 at 17:39 UTC
Right, but my reading of jhourcle's post is that if you have columns named "id" in both tables, Oracle will automatically say "Oh, look - two columns with the same name! I'll be helpful and automatically JOIN them!" If this is the case (I'm not that familiar with Oracle, so I may be misunderstanding what he meant), then it doesn't matter how you refer to them in your query, the DB's misguided attempt to be "helpful" will still cause problems.	[reply]