MYSQL inappropriate query

Question

I have following two queries and their out comes are different but what I want is as following:

I have two tables:

Subject:
-subject_id (Primary key)
-about
-details

feedback:
-id (Primary Key)
-subject_id (Foreign key)
-rating
-DateAndTime

Following are the queries and their result in words:

SELECT distinct about, details, subject.subject_id, round(AVG(rating),2) as Rating, 
        Max(DATE_FORMAT( DateAndTime, '%d-%m-%Y' )) as Date,
        Max(TIME_FORMAT( DateAndTime, '%h:%i:%s' )) as Time  
FROM `subject` , `feedback` 
WHERE subject.Subject_ID = feedback.Subject_ID 
GROUP BY about,details,subject_id
ORDER BY DateAndTime DESC

Here in this query the output is unique about,details,subject_id column and average rating. But Problem is with date and time. I want the last date and time entered for that result and result also contains that but it isn't in ordered manner.

above query's image

When i perform this query it gives perfect order but the rating gets revised

SELECT distinct about, details, subject.subject_id, round(AVG(rating),2) as Rating, 
        Max(DATE_FORMAT( DateAndTime, '%d-%m-%Y' )) as Date,
        Max(TIME_FORMAT( DateAndTime, '%h:%i:%s' )) as Time  
FROM `subject` , `feedback` 
WHERE subject.Subject_ID = feedback.Subject_ID 
GROUP BY about,details,subject_id,dateandtime  
ORDER BY DateAndTime,Time DESC

The difference is just in group by clause.

So anyone could help me please.

It's almost never right to use both `SELECT DISTINCT` and `GROUP BY`. `GROUP BY` makes it distinct for those columns. — Barmar, Oct 26 '16 at 19:03
If you want to get newest record check this one: http://stackoverflow.com/questions/8523374/mysql-get-most-recent-record — Radosław Halicki, Oct 26 '16 at 19:04
Do you really need `Date` and `Time` to be in separate columns? You're getting the maximum time, ignoring the date. So if one of the times is `2016-10-20 17:00` and another is `2016-10-21 14:00`, the maximum time will be `17:00`, not `14:00`. If you make them a single column you'll get the max of both. — Barmar, Oct 26 '16 at 19:06
@RadosławHalicki I have already used that syntax. but I want to find new records from two table. And the link is for just one column. And they are totally different scenario — , Oct 26 '16 at 19:06
@Barmar I know that.. It just how my query works. Its not the issue, Its just formatting the date and time different and not doing that programatically — , Oct 26 '16 at 19:07
You don`t need to seperate Date from Time, this is the issue here i think. Format it in one column. DATE_FORMAT(DateAndTime,'%d-%m-%Y %h:%i:%s'). — Radosław Halicki, Oct 26 '16 at 19:08
@Barmar: mysql doesn't require all selected fields be grouped or aggregated, and `distinct` works on a per-row basis, not per-row. — Marc B, Oct 26 '16 at 19:47

Barmar · Answer 1 · 2016-10-26T19:51:35.457

0

When you put the day first in the date format, MAX() will select the date with the highest day number, which isn't necessarily the most recent date. For instance, 26-10-2016 is higher than 21-11-2017 because 26 is higher than 21. To order by a formatted date, it has to be in %Y-%m-%d format.

And when you select the maximum time after formatting, you're getting the highest time ignoring the date entirely.

Instead of applying MAX() to the result of DATE_FORMAT, get the maximum DateAndTime and format that as desired:

DATE_FORMAT(MAX(DateAndTime, '%d-%m=%Y')) AS Date,
DATE_FORMAT(MAX(DateAndTime, '%h:%i:%s')) AS Time

To order the results by the displayed date and time, use:

ORDER BY STR_TO_DATE('%d-%m-%Y %h:%i:%s', CONCAT(Date, ' ', Time)) DESC

And you shouldn't have DateAndTime in the GROUP BY, because then you'll get a separate group for each time. You need to combine all the times into a single group so you can then get the last value for it with MAX(DateAndTime).

edited Oct 26 '16 at 19:51

answered Oct 26 '16 at 19:12

Barmar

741,623
53
500
612

here when i am using max clause in date_format or time_format it applies to whole date and time. Just the problem i am facing is ordering issue. Here order isn't valid. I will attch the images – Oct 26 '16 at 19:21
Take `DateAndTime` out of `GROUP BY`. – Barmar Oct 26 '16 at 19:25
Use `ORDER BY Date DESC, Time DESC`. – Barmar Oct 26 '16 at 19:38
I I will do the this then the thing you told will arise. 30th date will be the highest and 01 will be lowest even though 01 is in 2017 and 30 is in 2016.. – Oct 26 '16 at 19:43
You have to do the sort PRIOR to the format. Formatting in MySQL casts the result as a string. In other words, you need to wrap the query in an outer select while doing the ordering in the inline view. – T Gray Oct 26 '16 at 19:48
@Jay Oops, forgot about that. I've changed it to use `STR_TO_DATE` to convert the poorly-formatted date back into a proper date/time for ordering. – Barmar Oct 26 '16 at 19:52
SELECT about,details,s.subject_id, s.Rating, Date, Time FROM (SELECT distinct about, details, subject.subject_id, round(AVG(rating),2) as Rating, Max(DATE_FORMAT( DateAndTime, '%d-%m-%Y' )) as Date,Max(TIME_FORMAT( DateAndTime, '%h:%i:%s' )) as Time FROM `subject` , `feedback` WHERE subject.Subject_ID = feedback.Subject_ID GROUP BY about,details,subject_id) s ORDER BY STR_TO_DATE( Date, '%d-%m-%Y') desc, STR_TO_Date(Time, '%h:%i:%s') desc ..... This query worked. Thanks a lot – Oct 26 '16 at 20:14
The time doesn't need to be converted to a date, it already orders correctly. – Barmar Oct 26 '16 at 22:25

MYSQL inappropriate query

1 Answers1