fr600;10983399 wrote:
SELECT * FROM
( SELECT * FROM tablename ORDER BY id DESC )
tablename
GROUP BY MONTH(datecol)
ORDER BY datecol
This is not valid SQL syntax, which leads me to believe you are using MySQL which is the only DBMS I know that allows the use of a group by clause without having every selected field as either part of the group by clause or as part of an aggregate function. It will lead to unpredictable results! The reason you get something else here than you do in your first attempt, is that MySQL considers the table ordered in some particular fashion (and you should never count on a table being in any particular order unless you specify an order by clause), which does not match your order here. And, what mysql does when you have a query where one or more fields is NOT part of either the group by clause or an aggregate function is that it retrieves the first encountered value. Do NOT rely upon this.
Example of this
-- table: game
-- contains username and score
----------------
john 4
john 2
jane 0
jane 6
jane 3
SELECT name, score FROM game GROUP BY name;
-- What's supposed to happen here? You take all data, and for any rows sharing the
-- same name, group those selected rows into one row. However, for john, this
-- means that you might get either 4 or 2, and for jane the result could be either
-- 0, 6 or 3.
-- This is why SQL does not allow this type of ambigous nonsense
-- These are however correct SQL statements, since...
-- name is in the GROUP BY clause, score is part of the aggregate function MAX()
SELECT name, MAX(score) FROM game GROUP BY name
-- ... and suddenly it's clear which score to choose when grouping data
-- name is part of the group by clause and so is score
SELECT name, score FROM game GROUP BY name, score
-- ... which means you will actually get all of the entries on separate rows,
-- 2 for john and 3 for jane
-- This query will not work since there is no table called "comp_tablename" specified
-- in this query. The first line should read
-- SELECT tablename.*
SELECT comp_tablename.*
FROM tablename
INNER JOIN (
SELECT MAX(id) AS id
FROM tablename
GROUP BY MONTH(datecol)
-- this is the fix, since ordering this result set in DESC order
ORDER BY id DESC
-- in combination with taking the first 3 of these rows
LIMIT 3
) ids ON tablename.id = ids.id
ORDER BY id ASC
However, also note that this query relies on each later date also having a higher id. If this is always and unconditionally the case, you're fine. However, if this is just how things usually are without program logic to guarantee it, you can obviously NOT rely on selecting max id, but would have to go by date instead.
