Geek Answers Handbook

SELECT with identifier MAX from each GROUP BY (unique_id) and ORDER BY

I have a table with id, unique_id and order_number.

  • I want GROUP rows by unique_id
  • I want to take a line with MAX id from each group
  • And the last thing I want to sort by these lines is in order_name

I also have some WHERE clauses. This is my attempt that 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
+4
source share
2 answers

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 idnot 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
+12
source

Try the following:

SELECT id, redni_broj 
FROM table 
WHERE final=0 AND username='$username' AND active=1 AND
    id IN (
              SELECT MAX(id) FROM table table2
              WHERE table.unique_id = table2.unique_id
          )
GROUP BY unique_id 
ORDER BY order_number;
+2
source

More articles:


All Articles