Query in MySQL does not sort correctly

Let's start with this query here:

select id_plataforma from game_plataforma order by id DESC;

Note that you are selecting a field while you are sorting through another field. This should not be what you wanted. The id field that it uses to sort is just the order of insertion of the records in that table. This will only bring a list of platform ids that are related to some game any number of times they are listed and sorted according to the order in which they were entered. This will not bring anything useful to you.

Already this query here:

select distinct(id_plataforma) from game_plataforma order by id DESC;

This brings something similar to the previous query, but without repetitions. A list of platform ids that are related to any game at least once, sorted according to the order in which they were entered. It's also not very useful there.

select id_plataforma from game_plataforma group by id_plataforma order by id DESC;

This group by will be just a bizarre way of doing distinct of the previous query.

Well, let's leave those tests aside and look at your original query:

select DISTINCT(p.id), p.slug, p.nome,gp.id_plataforma
                            from game g
                            join game_plataforma gp on g.id=gp.id_game
                            join plataformas p on gp.id_plataforma=p.id
                            where g.ativo='1'
                            group by p.nome
                            order by g.dia DESC
                            limit 4

You're listing platform data, but you end up going to the game table to get them. If it were not for group by and distinct , the number of results would probably be the number of records in the game_plataforma table, which means several times the same platforms multiplied by the number of active games on each platform. >

Also, it seems like you do not understand how DISTINCT works. DISTINCT applies to the entire set of selected columns, not just the one in parentheses. In fact, these parentheses are doing absolutely nothing.

I think what you wanted after all is this:

SELECT p.id, p.slug, p.nome
FROM game g
INNER JOIN game_plataforma gp ON g.id = gp.id_game
INNER JOIN plataformas p ON gp.id_plataforma = p.id
WHERE g.ativo = '1'
GROUP BY p.id
ORDER BY MAX(gp.id) DESC

The trick here is that you only want platform information, but just like in your original query, you have to navigate to the game table to get the ativo field (which also already eliminates the platforms for which there are no games). This would then replicate the platform information, were it not for our GROUP BY .

For GROUP BY to work, it is important that no field of the gp or g tables appear in SELECT or ORDER BY without using some aggregating function. Otherwise, they would mess with the GROUP BY that throws all of them into a single record. The purpose of aggregator functions is to merge several records into one so they can be used when there is a GROUP BY .

However, to sort in ORDER BY I need information from the gp table (which is the most recent), and therefore I need some aggregating function. The aggregate function used in ORDER BY is the MAX function that chooses the highest value among all those that will be aggregated, in this case the largest id of the gp table. Since the id s are sorted by insertion order, then the highest id is the most recent.