MySQL Inner Join Query Only Returning DISTINCT VALUES

Question

The following query is intended for a pagination script that is to return news posts from a DB. It works, but does not return any items with same dates, or title.

How do I rewrite this in order to get items with same title and/or same date?

Query:

"SELECT p.post_id, p.post_title, p.post_detail, p.post_user, p.post_slug, p.post_date, p.post_type 
 FROM ".TBL_POSTS." as p 
 LEFT JOIN ".TBL_POST_CATEGORIES." as c
 ON c.post_ID = p.post_id
 WHERE p.post_type = 'post' AND c.category_ID = 5
 GROUP BY p.post_date DESC", 10);

Here is an example of the POST table

--------------------------------------------
post_id | post_title  | post_date
--------------------------------------------
10      | news post 1 | 2014-02-13 21:09:45
--------------------------------------------
12      | news post 2 | 2014-02-14 21:09:45
--------------------------------------------

Here is an example of the CATEGORIES table

--------------------------------------------
id      | post_ID  | category_ID
--------------------------------------------
1       | 10       | 5
--------------------------------------------
2       | 12       | 5
--------------------------------------------

Make sure that your columns in where clause has correct data, i.e. remove where clause and then try to execute. — AK47
– AK47, Commented Mar 15, 2014 at 5:00
@AK47 the issue was with the (GROUP BY) I should've used (ORDER BY). Thank you. — Cosmo
– Cosmo, Commented Mar 15, 2014 at 5:28
@CosmoGroup by is not required at all, you are correct. but that is not correct reason for wrong output.Even though date col. is in group by but it will grouped by full date including hh:mm:sss, record which has difference in seconds will be also treated sepretly — AK47
– AK47, Commented Mar 15, 2014 at 5:29

Community · Accepted Answer · 2017-05-23 10:25:04Z

3

The problem is the GROUP BY usage.

SQL only returns one record per distinct grouped column values - every other column, not in the group by clause, should be in an aggregate function.

Unfortunately, MySQL silently accepts this incorrect query (which has been the source of many bugs) and selects a record "at random" from the grouping. In this case, since only post_date is in the grouping, there will be only one record per unique date/time.

I suspect a "correct" behavior is simply to remove the GROUP BY (perhaps replacing it with ORDER BY), although this might not be the intent - updating the post with expected output could lead to useful suggestions on how to update the query.

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Mar 15, 2014 at 5:01

user2864740

62.5k15 gold badges159 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

AK47 Over a year ago

even though date col. is in group by but it will grouped by full date including hh:mm:sss, record which has difference in seconds will be also treated sepretly.

Cosmo Over a year ago

@user2864740 How else would I get this query to sort by descending date or ascending date without the group by?

user2864740 Over a year ago

@Cosmo Just sort on (ORDER BY) the field; what is with the grouping? (Without a hierarchical format like XML, which is provided by some vendors, SQL can only return tabular data and GROUP BY still results in tabular output.)

Cosmo Over a year ago

@user2864740 Thank you that solved it. Not sure I understand your question "what is with the grouping?". The grouping is pull events from oldest to newest with the option of doing vice versa. I hope I understood your question.

user2864740 Over a year ago

@Cosmo I meant, "Why do the results need to be grouped?" I am glad you were able to solve your problem.

|

Collectives™ on Stack Overflow

MySQL Inner Join Query Only Returning DISTINCT VALUES

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related