Grouping the largest ID per line does not take up correct values

Asked

Viewed 105 times

1

I have the following table:

Tabela: tabela_1

id   | id_pessoas | id_empreitadas
-----------------------------------
4357 | 3776       | 37
3949 | 3776       | 3

I have the appointment:

SELECT id_pessoas , MAX(id) max_id, id_empreitadas FROM tabela_1 GROUP BY id_pessoas

This consultation resumes:

id   | id_pessoas | id_empreitadas
-----------------------------------
4357 | 3776       | 3

I can’t understand why it takes up the value 3 in the id_empreitadas and not the 37. Can you help me?

mysql

  • What you need is the highest id_contract value or do you need the value related to the record that has the highest id? That’s not clear in the question.

  • It doesn’t really bring anything, because the GROUP BY make a mistake.

  • Basically pq is not how it works. In Sqlite the line returned is always the same in all fields. In Mysql you can return any one. @Rbz depending on configuration. As it is, you need ANY_VALUE( ) to return the others, without Strict it is as if you had applied ANY_VALUE() to them without grouping

  • The submitted query is incorrect, because in group by you should put all the entries in select. Send the real query you used that returned this record.

  • What I want is the line data that contains the highest id value. When the query is executed, it resumes the row with the largest id, but the value that resumes in the 'id_contractors' column is the value of another row. The goal is to get the 'id_contractors' column at the highest id value'.

2 answers

5

The problem is that nothing guarantees in Mysql that columns without aggregation will return from the same row.

Including, as noted by @Rbz in the comments, depending on configuration Mysql will give error by mixing aggregation with normal line.

A possible solution is to make a JOIN between the table, and a virtual resulting from the MAX grouped from it.

First, we make this virtual table with:

SELECT MAX(id) AS id FROM tabela_1 GROUP BY id_pessoas

that returns a list of Ids with the desired criterion (the largest of each group).

Then we make a SELECT of all the columns we want, and a JOIN using the return of SELECT previous as a virtual table, thus:

SELECT
   id_pessoas,
   id,
   id_empreitadas
FROM
   tabela_1 a
   JOIN (SELECT MAX(id) AS id FROM tabela_1 GROUP BY id_pessoas) b
   USING (id);

See working on SQL Fiddle.

2

There is another way beyond reply that @Bacco gave that is to use the IN:

SELECT
   id_pessoas,
   id,
   id_empreitadas
FROM
   tabela_1
   WHERE id IN (SELECT MAX(id) AS id FROM tabela_1 GROUP BY id_pessoas);

See working on SQL Fiddle

  • 1

    If anyone is interested: https://explainextended.com/2009/06/16/in-vs-join-vs-exists/ - To sum up, according to the author of the article, Planner ends up optimizing IN to get the same execution plan of Join to take advantage of indexes at the end, avoid repeating SELECT to each line.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.