Alex Rivera | Logout

MySQL: How does groupby work on columns without aggregate functions?

Asked 2010-11-14T17:57:04.167
9

I am somewhat confused about how the group by command works in mysql.

Suppose I have a table: 

mysql> select recordID, IPAddress, date, httpMethod from Log_Analysis_Records_dalhousieShort;                   
+----------+-----------------+---------------------+-------------------------------------------------+
| recordID | IPAddress       | date                | httpMethod                                      |
+----------+-----------------+---------------------+-------------------------------------------------+
|        1 | 64.68.88.22     | 2003-07-09 00:00:21 | GET /news/science/cancer.shtml HTTP/1.0         | 
|        2 | 64.68.88.166    | 2003-07-09 00:00:55 | GET /news/internet/xml.shtml HTTP/1.0           | 
|        3 | 129.173.177.214 | 2003-07-09 00:01:23 | GET / HTTP/1.1                                  | 
|        4 | 129.173.177.214 | 2003-07-09 00:01:23 | GET /include/fcs_style.css HTTP/1.1             | 
|        5 | 129.173.177.214 | 2003-07-09 00:01:23 | GET /include/main_page.css HTTP/1.1             | 
|        6 | 129.173.177.214 | 2003-07-09 00:01:23 | GET /images/bigportaltopbanner.gif HTTP/1.1     | 
|        7 | 129.173.177.214 | 2003-07-09 00:01:23 | GET /images/right_1.jpg HTTP/1.1                | 
|        8 | 64.68.88.165    | 2003-07-09 00:02:43 | GET /studentservices/responsible.shtml HTTP/1.0 | 
|        9 | 64.68.88.165    | 2003-07-09 00:02:44 | GET /news/sports/basketball.shtml HTTP/1.0      | 
|       10 | 64.68.88.34     | 2003-07-09 00:02:46 | GET /news/science/space.shtml HTTP/1.0          | 
|       11 | 129.173.159.98  | 2003-07-09 00:03:46 | GET / HTTP/1.1                                  | 
|       12 | 129.173.159.98  | 2003-07-09 00:03:46 | GET /include/fcs_style.css HTTP/1.1             | 
|       13 | 129.173.159.98  | 2003-07-09 00:03:46 | GET /include/main_page.css HTTP/1.1             | 
|       14 | 129.173.159.98  | 2003-07-09 00:03:48 | GET /images/bigportaltopbanner.gi
mysql group-by
Edit
Report

1 Answer

5

Because I'm new apparently I can't post helpful images so I'll try to do this with text...

I just tested this and it appears that the values of fields that are NOT in the GROUP BY will use the values of the FIRST row that matches the group by condition. This will also explain the perceived "randomness" that others have experienced with selecting columns that aren't in a group by clause.

Example:

Create a table called "test" with 2 columns called "col1" and "col2" with data that looks like this:

Col1 Col2
1 2
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

Then run the following query:

select col1,col2
from test
order by col2 desc

You will get this result:

1 3
2 3
3 3
 1 2
1 2
2 2
3 2
2 1
3 1

Now consider the following query:

select groupTable.col1,groupTable.col2
from (
   select col1,col2
   from test
   order by col2 desc
) groupTable
group by groupTable.col1
order by groupTable.col1 desc

You will get this result:

3 3
2 3
1 3

Change the subquery to asc:

select col1,col2
from test
order by col2 asc

Result:

2 1
3 1
1 2
 1 2
2 2
3 2
1 3
2 3
3 3

Again use that as the basis for your subquery:

select groupTable.col1,groupTable.col2
from (
   select col1,col2
   from test
   order by col2 asc
) groupTable
group by groupTable.col1
order by groupTable.col1 desc

Result:
3 1
2 1
1 2

Now you should be able to see how the order of the subquery affects which values are chosen for fields that are

answered 2012-07-17T19:31:06.220

Your Answer