CODEWITHSUNDEEP
mysqlgroup-by
R.M.D
R.M.D

Reputation: 25

MySQL: GROUP by with multiple columns

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:

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 ?';

Upvotes: 1

Views: 325

Answers (2)

ivanm
ivanm

Reputation: 138

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.

Upvotes: 1

DRapp
DRapp

Reputation: 48179

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

Upvotes: 1

Related Questions