Why SQL query with GROUP BY creates more rows?
I have the following table:
+------+-------+--------------------------------------+
| id | rev | content |
+------+-------+--------------------------------------+
| 1 | 1 | ... |
| 2 | 1 | ... |
| 1 | 2 | ... |
| 1 | 3 | ... |
+------+-------+--------------------------------------+
When I run the following query:
SELECT id, MAX(rev) maxrev, content
FROM YourTable
GROUP BY id;
I get:
+------+----------+--------------------------------------+
| id | maxrev | content |
+------+----------+--------------------------------------+
| 1 | 3 | ... |
| 2 | 1 | ... |
+------+----------+--------------------------------------+
But if I remove the GROUP BY clause like this:
SELECT id, MAX(rev) maxrev, content
FROM YourTable;
I get:
+------+----------+--------------------------------------+
| id | maxrev | content |
+------+----------+--------------------------------------+
| 1 | 3 | ... |
+------+----------+--------------------------------------+
This is counterintuitive due to the expectation that GROUP BY will reduce the number of results by eliminating duplicate values. However, in the above case, introducing GROUP BY does the opposite. This is because of the MAX () function, and if so, how?
PS: The table is based on the SO question here: SQL only selects the rows with the maximum value in the column . I was trying to figure out the answer to this question and in the process ran into the above situation.
EDIT:
I got the above results on sqlfiddle.com using its MySQL 5.6, no setup / configuration.
source to share
Uses your MAX () function depending on your GROUP BY clause. So, for your first query, you say, "Give me the maximum rev for each id , while the second one is just say" Give me the maximum rev at all .
Thanks to xQbert:
This means it does NOT mean you are getting the stringwith maximum turnover in the latter case. It will take values from any selection point to use for your id and content fields.
You can read more about how SQL handles the GROUP BY clause here: Documentation
source to share
This is because you are using the previuos version that mysql is 5.7. This version allows you to use the aggregate function d and select columns not in a group ... this results in an imredicatble result for a non-aggregated column .. in mysql 5.7 this beahvior is not allowed ... you have an error if you selected a non-aggregated a function not mentioned in the group,
correct syntax is obvious first
SELECT id, MAX(rev) maxrev, content
FROM YourTable
GROUP BY id;
source to share
SELECT id, MAX(rev) maxrev, content FROM YourTable
GROUP BY id;
When you run this, since there are 2 different ids in the table, you end up with two rows in the result, one per id with the maximum value. Grouping occurs in the id column.
SELECT id, MAX(rev) maxrev, content
FROM YourTable;
If you remove the group by clause, you only get one row in the result, corresponding to the maximum value in the entire table. No grouping by id.
source to share