SELECT row with MAX id from each GROUP BY (unique_id) and ORDER BY number

Question

I have table with id, unique_id, and order_number.

1. I want to GROUP rows by unique_id
2. I want to take row with MAX id from each group
3. And last thing I want to sort that rows by order_number

Also I have few WHERE clauses. This is my attempt which does not work:

SELECT MAX(id) AS id
     , order_number
  FROM table 
 WHERE final = 0 
   AND username = '$username' 
   AND active = 1 
 GROUP 
    BY unique_id 
 ORDER 
     BY order_number

Answer 1

You can use your query as a subquery:

SELECT *
FROM table 
WHERE id IN (SELECT MAX(id) AS id
             FROM table 
             WHERE final=0 AND username='$username' AND active=1 
             GROUP BY unique_id) 
ORDER BY order_number

or, if id is not unique, use JOIN:

SELECT t1.*
FROM table AS t1
JOIN (SELECT MAX(id) AS max_id, unique_id
      FROM table           
      WHERE final=0 AND username='$username' AND active=1 
      GROUP BY unique_id
) AS t2 ON t1.unique_id = t2.unique_id AND t1.id = t2.unique_id
ORDER BY order_number