MySQL: GROUP by with multiple columns

Question

I am trying to fetch multiple values from a table, with a GROUP BY targeting only one of the columns. To simplify things, I'll use a mockup guestbook table as an example:

postNumber | user | text        | date | portal
-----------------------------------------------
 1 | Michael      | "nice blog" | 10/12| fitness
 2 | Ronald       | "hello!"    | 12/12| lounge
 1 | Michael      | "beautiful" | 05/11| pictures
 2 | Sandra       | "great pic" | 10/12| pictures

Let's assume the frontpage of the guestbook must print the latest guest posts, but only one per portal (fitness, lounge and pictures). The desired outcome would be:

michael/"nice blog"/fitness
ronald/"hello"/lounge
and sandra/"great pic"/pictures.

The excluded post wold be Michael/"beautiful"/pictures, since it is not the latest one of that portal.

The following query is invalid with more modern mySQL servers, throwing the error described below:

SELECT max(postNumber), user, text, date, portal FROM guestbook GROUP BY portal;

GROUP BY clause and contains nonaggregated column 'database.table.columnName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

However, this more limited query below works in achievement the desired outcome, but it cannot fecth the remaining needed columns (user,text,date), which is what I'm having trouble with precisely:

SELECT max(postNumber), portal FROM guestbook GROUP BY portal;

It should be noted that the first query can be fixed by adding user,text and date to GROUP BY, but doing a composite grouping like that would no longer retrieve only one entry per portal.

If anyone has a solution to this problem, please elaborate as I have been unable to find a way by myself or through online research.

Edit: I have attempted the following query, with no results as the RIGHT JOIN is giving me the values from the left table (only portal and post number), unsure as of why:

'SELECT t1.portal, max(t1.postNumber) from guestbook t1 RIGHT OUTER JOIN (SELECT * from guestbook) t2 on t1.postNumber = t2.postNumber GROUP BY portal LIMIT ?';

You can try disabling only_full_group_by as described [in this answer](http://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — jonesy827, Nov 30 '16 at 16:33
I understand, bu the reason only_full_group_by is default now, to my knowledge, is because otherwise it may produce loose, random results and it is best to make a more sturdy SQL. If possible at all, I'd like to implement a solution that is compatible with the new mySQL default setting — R.M.D, Nov 30 '16 at 16:35

score 1 · Accepted Answer · answered Nov 30 '16 at 17:29

1

You are on the right track... Do a pre-query as a basis of what records you want, then re-join to the same original table on the qualifying date condition..

select
      PreQuery.Portal,
      PreQuery.LatestPostNumber,
      T2.User,
      T2.Text,
      T2.Date
   from
      ( select 
              t1.Portal,
              max( t1.PostNumber ) as LatestPostNumber
           from
              GuestBook T1
           group by 
              t1.Portal ) PreQuery
         JOIN GuestBook T2
            on PreQuery.Portal = T2.Portal
           AND PreQuery.PostNumber = T2.PostNumber

answered Nov 30 '16 at 17:29

DRapp

47,638
12
72
142

Thank you so much DRapp. While your query itself returned 'zero' on my localhost (possibly due to my fault), your example made things clearer for me. I needed an extra "AND" so my queries would JOIN properly.This is my current query that so far is returning expected results: 'SELECT t1.parentID, t1.imageFileExtension, t1.postNumber, t1.postBoardDirectory, t1.user FROM posts t1 JOIN (SELECT postBoardDirectory as PBD, max(postNumber) as MAXNUM from posts GROUP BY PBD ) t2 on t1.postNumber = MAXNUM AND t1.postBoardDirectory = PBD LIMIT 15'; if I run into new query issues I'll update this post – R.M.D Nov 30 '16 at 19:00
1

@R.M.D, glad to help, and also, take a look at help -> tour for site etiquette on this and your other questions. Mark as answered IF the post actually worked, or directly helped your questions resolution (like this one). Then if others have similar issues they too will see what worked and why. – DRapp Dec 01 '16 at 03:25
1

@R.M.D, one additional point. Don't use comments for code samples. Edit your original post, put the changes there, then just add a comment to ask the person to review your changes in case you need additional follow-up. – DRapp Dec 01 '16 at 16:57

ivanm · Answer 2 · 2016-12-02T07:17:16.313

Please try the following query

SELECT *
FROM (SELECT postNumber, 
             user, 
             text,
             date,
             @current_portal := portal,
             @portal_rank := IF(@current_portal = portal and @portal_rank = portal, @portal_rank + 1, 1) AS portal_rank,
      FROM guestbook
      ORDER BY portal, date DESC) ranked
WHERE portal_rank = 1)

Rank the portal, using the portal_rank variable, and order by portal and date desc. In this way you are selecting only the latest record for every portal

I think this query is easier to understand and there is no need to do multiple joins on a single table when you can use a variable instead.

Additionally switching the condition portal_rank = 1 with portal_rank lets say <= 3 you can get 3 latest records per portal with minimum query changes.

Hope this helps.

MySQL: GROUP by with multiple columns

2 Answers2