Query in Mysql does not order correctly

Question

I have a game table called game, a table of gaming platforms called plataformas and a table that makes the N-to-N relationship between the two calls game_plataforma.

I have a query that has to fetch a platform games, but has to sort according to the last game that was registered in it.

I have the following appointment:

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

Only it is not ordering correctly. It is ordering alphabetically by platform name.

I took the following test:

select id_plataforma from game_plataforma order by id DESC;

And it returned right these values down to the field id_plataforma:

2, 22, 2, 25, 2, 2, 2, 2, 25, 2, 1, 2, 2, 23, 6, 6, 22, 2, 22

Now, when I run this one:

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

He returns it here to the same fields:

25, 1, 23, 6, 2, 22

And with group by comes to that:

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

25, 1, 23, 6, 2, 22

I’m going crazy or is there something wrong?

Table structure game_plataforma:

what I need to come are the platform ids in this order:

2, 22, 25, 1, 23

Answer 1

Let’s start with this consultation here:

select id_plataforma from game_plataforma order by id DESC;

Note that you are selecting a field while ordering for another field. This should not be what you wanted. The field id 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 any game any number of times they are related and ordered according to the order in which they were entered. This won’t bring you anything useful.

Already this consultation here:

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

This brings something similar to the previous consultation, but without repetitions. A list of platform ids that are related to any game at least once, ordered according to the order in which they were inserted. It’s not very useful either.

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

That one group by will just be a bizarre way of doing the distinct of the previous consultation.

Well, let’s put 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 are listing data platforms, but end up going to the table of games to get them. If it were not for the group by and by distinct, the number of results would probably be the number of records in the table game_plataforma, which means several times the same platforms multiplied by the number of active games on each platform.

Besides, you don’t seem to understand how the DISTINCT. The DISTINCT applies to the entire set of selected columns, not just the one in parentheses. By the way, these parentheses are doing absolutely nothing.

I guess 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 as in your original query, you need to navigate to the table of games to there catch the field ativo (which also eliminates platforms for which there are no games). That would repeat the platform information, were it not for our GROUP BY.

To the GROUP BY work, it is important that no field of the tables gp or g appear in the SELECT or in the ORDER BY without using any aggregator function. Otherwise, they would mess with the GROUP BY who plays them all in one record. The purpose of the aggregator functions is precisely to join multiple records into one so that they can be used when there is one GROUP BY.

However, to make the ordination in the ORDER BY i need a table information gp (which is the most recent), and therefore I need some aggregator function. The aggregator function used in the ORDER BY is the function MAX that chooses the highest value of all those that will be aggregated, in this case the highest id table gp. How the ids are ordered in order of insertion, so the largest id is the latest.