MYSQL Select statment Order By with Group By

Question

I have the following simple SQL statment

SELECT id, name, value_name, value_id
FROM table
GROUP BY id
ORDER BY value_id DESC

when grouping I would like to get the value_name and value_id of the tuple where the value_id is the biggest. The way it is i am getting the smallest value. For example

1, name1, valuename, 3     (where i know that there is a value_id of 5)

Can you please help?

Answer 1

change , value_id to , MAX(value_id) AS value_id in the field list...

Actually, now that I look at it, I think what you want is something like this:

SELECT a.id, a.name, MAX(b.value_id) AS value_id, (SELECT b.value_name FROM table AS b WHERE b.id = a.id AND b.value_id = MAX(a.value_id)) AS value_name
FROM table AS a 
GROUP BY a.id, a.name
ORDER BY value_id DESC

The problem is that the table isn't fully normalized. So you have multiple rows with id and name being the same, but each has a unique value_id and value_name. So the only ways to get the value_name associated with the value_id is either through a subquery (what I did there), or a join...

Answer 2

SELECT id, name, value_name, MAX(value_id )
FROM table 
GROUP BY id, name, value_name
ORDER BY value_id DESC

Answer 3

You can get biggest value_id by this statement:

SELECT id, name, value_name, value_id
FROM table
GROUP BY value_id 
HAVING max(value_id)

If you want to get other columns you can using a sub query with it.