mySQL Query Help

[Spire2000]

I have a table, we'll call it "status". This table has many rows being entered constantly through user updates. It has many columns, but the two we are concerned with here are a "date" column, and a "group" column.

I'd like to create a query that returns only the most recent entry per group, and ordering the groups alphabetically. I'm racking my brain on this one, but can't figure it out.

The best I can come up with is:

"SELECT * FROM status ORDER BY group DESC LIMIT 1"

But all that does is return one record total, not one record per group. To be clear, each row has a group name entered.

Anyone have any ideas? Any help would be greatly appreciated.

[Spire2000]

Resolved

SELECT group, MAX(date) AS max_date FROM status GROUP BY group ORDER BY goup

[scoutt]

you need to be careful on using reserverd wrods for mysql. you shouldn't use "status" and "group" as column/table names, it will cause you problems.

[putts]

Originally posted by scoutt
you need to be careful on using reserverd wrods for mysql. you shouldn't use "status" and "group" as column/table names, it will cause you problems.

Unless you really really LOOOOOOVEE the ole `. :P

Same goes for DB2, MS SQL....whatever.

If you are one of those guys who ALWAYS puts ``, [] or "" around your fieldnames, then call em whatever you want but I'm guessing it'd still find a way to bite you in the butt.

[Spire2000]

you need to be careful on using reserverd wrods for mysql. you shouldn't use "status" and "group" as column/table names, it will cause you problems.

Thanks for the tip, but I was just using those column names as placeholders for my true column names. I work on a system where the data is not allowed to be shared outside of it, so I err on the side of caution when it comes to things like this.

[scoutt]

that is undertandable. glad I can help :)