Using multiple JOIN's and GROUP BY - ORDER BY not working as expected

Question

SELECT msg.msgFrom, 
       mem.memberID,
       mem.memberFirstName,
       mem.memberLastName,
       msg.msgJobID,
       msg.msgMessage,
       msg.msgRead,
       job.jobDescription 
FROM messages msg JOIN members mem ON msg.msgFrom = mem.memberID 
JOIN jobs job on msg.msgJobID = job.jobID 
WHERE msgTo = ? 
GROUP BY msgJobID 
ORDER BY msg.msgRead DESC

The ORDER BY in my MySQL statement doesn't seem to be working. Just wondering if someone could help me out.

Thanks

That GROUP BY is invalid. Will not execute with newer MySQL versions (unless in compatibility mode.) The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! (And why GROUP BY when no aggregate functions are used?) — jarlh, Dec 13 '16 at 10:46
The GROUP BY is working as expected, just the ORDER BY - but would appreciate if you could give me an example so that I can see where I'm maybe using it incorrectly. Thanks — Mike, Dec 13 '16 at 10:47
"The GROUP BY is working as expected" Plainly, that isn't true. — Strawberry, Dec 13 '16 at 10:48
"each column reference in the SELECT list must either identify a grouping column or be the argument of a set function" — jarlh, Dec 13 '16 at 10:48
It is, it's job is to group messages together by their job id. That is the result — Mike, Dec 13 '16 at 10:49
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 13 '16 at 10:51
There is... GROUP BY isn't the issue, it's the ORDER BY. I understand the issue may be a result of both of them used incorrectly - but that's why I've came here — Mike, Dec 13 '16 at 10:51
I think that mysql allow to use GROUP BY as this query. But, you should explain the desired and actual result. — McNets, Dec 13 '16 at 10:55
My desired result is to group the messages by their job id (which is working) - but then order those results by messages that have not been read yet, so that unread messages are listed first — Mike, Dec 13 '16 at 10:57
Currently I've got the desired result in terms of grouping, but the unread messages aren't ordering correctly — Mike, Dec 13 '16 at 10:58
The underlying problem is completely valid and actually relates to a mysql specifics - and does not justify the downvotes. He does not know what it is which is why he cannot provide the isolated reproductible scenario. I'll attempt posting an explanation in a bit. — Sebas, Dec 13 '16 at 11:08
@Sebas Is it appropriate to tell others how to exercise their vote? — Strawberry, Dec 13 '16 at 11:23
@Strawberry I suppose giving your opinion about it, if it is constructive, is always appropriate. Often, downvotes suffer a 'waterfall' effect and end up burrying a not-so-obvious interesting question. I thought it was the case here so I gave my opinion. Feel free to ignore it. — Sebas, Dec 13 '16 at 11:29
@Sebas It's suggested that downvotes are for questions which are unclear or not useful. IMO questions of this nature which fail to provide an MCVE fall into that category. — Strawberry, Dec 13 '16 at 11:49
I'm reviewing a close request for *Off-topic* on this post and I'm failing to see how this is off topic. OP asked a valid question and somehow got a bunch of down votes. Sooo leaving it open. — The Muffin Man, Feb 28 '20 at 20:44

score 3 · Answer 1 · edited Dec 13 '16 at 11:05

3

You are using grouping for no reason here. GROUP BY is used for aggregation (SUM,MAX,MIN,etc).

You cannot order by the field you are using as a result of your unnecessary grouping.

Also, define your join types (good practice)

Try this instead:

SELECT msg.msgFrom, 
       mem.memberID,
       mem.memberFirstName,
       mem.memberLastName,
       msg.msgJobID,
       msg.msgMessage,
       msg.msgRead,
       job.jobDescription 
FROM messages msg 
INNER JOIN members mem 
  ON msg.msgFrom = mem.memberID 
INNER JOIN jobs job 
  on msg.msgJobID = job.jobID 
WHERE msgTo = ? 
ORDER BY msgJobID, msg.msgRead DESC -- Use 2x order by's

edited Dec 13 '16 at 11:05

Sebas

21,192
9
55
109

answered Dec 13 '16 at 11:02

JohnHC

10,935
1
24
40

1

It's just a side comment please don't mind me - but he didn't use `INNER` in his original query. I don't see why you'd add it in this one. – Sebas Dec 13 '16 at 11:06
http://stackoverflow.com/a/15892024/1291428 - this is company policy to implement `JOIN` over `INNER JOIN` - there's absolutely no difference, except that `inner join` is 6 characters longer – Sebas Dec 13 '16 at 11:10
1

@Sebas, and JohnHC both. The INNER keyword is optional and ignored by MySQL. JOIN and INNER JOIN are identical. – Strawberry Dec 13 '16 at 11:12
@Strawberry, Sebas. I agree, but using `INNER` is easier to read – JohnHC Dec 13 '16 at 11:16
As it happens I don't agree, but of course you're welcome to use it if you think so. – Strawberry Dec 13 '16 at 11:19
This returns many results. The messages are no longer grouped by their job ID – Mike Dec 13 '16 at 12:38
1

@user3676945 consider the following: if you intent to remove duplicates, then use `DISTINCT` instead. However, it eliminates duplicates on the whole resultset, not just on one given attribute. But either way, you're stuck with an issue. Think about this: if you group by jobid, you tell mysql to pick arbitrarily a value for `msgRead`, which in turns prevents you from sorting. How do you expect `msgRead` to be calculated after grouping? This is a key answer you need to figure to establish a proper solution. – Sebas Dec 13 '16 at 14:57
@Sebas I know, it's a bit of a bummer. I do understand the problem, I'm just not aware of a way around it. Grouping is important, as without it, all messages within a 'thread (jobid)' would be shown individually. I'm not saying "GROUP BY" is the right way, I came here hoping for a little help and a nudge in the right direction. I appreciate your effort in trying to help me and I will take a look into DISTINCT as if I'm honest I've not used it before. Many thanks – Mike Dec 13 '16 at 18:39
@user3676945 it's important that before picking your tool you make sure you know what you want to display. Ad it stands the logic is flawed because you're trying to sort on an attribute that is flattened out (msgRead) by the grouping. I think the specs need simply to be revised and you'll find the solution immediately after that :) – Sebas Dec 14 '16 at 10:35

score 1 · Answer 2 · answered Dec 13 '16 at 11:19

Just to illustrate the effect of a group by, given

MariaDB [sandbox]> select * from person;
+-----------+-------+
| person_id | name  |
+-----------+-------+
|         1 | Test1 |
|         2 | Test2 |
|         3 | Test3 |
+-----------+-------+
3 rows in set (0.00 sec)

MariaDB [sandbox]> select * from personroles;
+-----------+---------+
| person_id | role_id |
+-----------+---------+
|         1 | 1       |
|         1 | 2       |
|         2 | 3       |
|         2 | 1       |
|         3 | 1       |
+-----------+---------+
5 rows in set (0.00 sec)

a group by on role_id returns 3 rows (instead of six) and the other columns in the select statement are not guaranteed to be the same in any 2 executions of the code.
so for example

MariaDB [sandbox]> select p.person_id,p.name,pr.role_id
    -> from person p
    -> join personroles pr on pr.person_id = p.person_id
    -> group by pr.role_id ;
+-----------+-------+---------+
| person_id | name  | role_id |
+-----------+-------+---------+
|         1 | Test1 | 1       |
|         1 | Test1 | 2       |
|         2 | Test2 | 3       |
+-----------+-------+---------+
3 rows in set (0.00 sec)

I suspect you just need an order by as JohnHC suggests.

score 0 · Answer 3 · answered Dec 13 '16 at 11:23

0

As per the documentation:

[...] If the [selected] columns are not functionally dependent on GROUP BY columns [...] the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses.

I'd recommend enabling ONLY_FULL_GROUP_BY on your server so your development is ANSI compliant. It will save you time in the future, as both your queries and your sql skills will become more portable over different dbms implementations.

answered Dec 13 '16 at 11:23

Sebas

21,192
9
55
109

Annoying performance considerations to one side, in the absence of any aggregating functions, GROUP BY serves no purpose in this query. – Strawberry Dec 13 '16 at 11:52
When I use GROUP BY, the results returned are exactly what I need. So to say it serves no purpose is wrong. I may very well be implementing it in a non-recommended way but the fact is it works on the basis of what I need. If I didn't require ORDER BY, it works perfectly. So unless you are able to offer more than just responses to peoples technicalities that don't progress a situation, it really is just a waste of bytes. I've viewed your other comments and all they seem to be is trying to prove a point for personal fulfillment and not aiding the actual question constructively in any way – Mike Dec 13 '16 at 12:52

Using multiple JOIN's and GROUP BY - ORDER BY not working as expected

3 Answers3