How to order by with group by in mysql

One way to do this is with a bunch of while loops and hitting the DB over and over, or selecting a crap load of data and just dropping out everything but the most recent inventory count manually. Both of these are viable solutions with a lot of overhead, but what I really want is just a silver bullet SQL statement that does this for me. I think I have it.

What I initially envisioned was:

SELECT *
FROM INVENTORY
ORDER BY vendors_no,
timestamp DESC
GROUP BY vendors_no,
product_no

However, if you are familiar with MySQL, you will know that you cannot put ORDER BY before GROUP BY. You have to GROUP BY and then ORDER BY.

It appears that the way that GROUP BY works is that it takes the first row of each “group” and just drops the rest. So whichever row happens to be first in the DB when the SQL is executing will get grabbed and the rest will get dropped. If I had wanted to get the first inventory count for each vendor/product, this would have been ok, since those would have been the first row, but it’s going to be the last row each time. So what to do? If only I could flip the whole table on it’s head or something…

Actually, I kind of can! This is what I came up with:

SELECT *
FROM(
SELECT *
FROM INVENTORY
ORDER BY vendors_no,
timestamp DESC
) as inv
GROUP BY vendors_no,
product_no

If I understand GROUP BY correctly and ORDER BY correctly (which I very well may not) the sub-query will first basically create a temporary table with the results in reverse timestamp order. Then when GROUP BY is going through and finding the first vendor_no and dropping the rest it will be keeping the most recent one.