PHP/MySQL SELECT MAX()?

Question

I need to fetch a single result from my database that is that Maximum value of a single column, similar to SELECT MAX() but here's the catch, the table is set up so it has a column for "order" (as in arrangement within a page: 1 being top of the list and so on). The data is also sub divided into groups which is represented by a "groups" column, so "order" is relative strictly within a group. I need the query to be able to select the max value of "order" but only within the confines of "group x"

Answer 1

Your initial setup is totally incorrect. If you have sub groups you should use a groups table with an auto_increment id as primary key. Then link the data by adding a groups key maybe called group_id to the first table. You should never need the order column, as this can be set each time the data is displayed. You can sort by multiple fields. As you are you have absolutely no flexibility in your use of the data, and might as well be using a flat file to store the information.

Answer 2

What is the data type of my field, based on the information you're provided that's the only problem I can think of. If it's not a numeric data type that could be the problem. FYI: that may have done the trick but his correction to your SQL was entierly unneccessary, and much less efficent, there's no reason to pull all the data in the table with the *. Your issue must have been how you were trying to return the variable from the sql results.

Answer 3

select max(order) from TABLE

where group = ...