2

I have a web application written with PHP and MySQL.

The result of one SQL command (mentioned below) is something like this:

enter image description here

SELECT
   t.subject, c.parent_id, c.date_time 
FROM
   tbl_tickets_contents c
JOIN
   tbl_tickets t
ON
   t.id = c.parent_id
ORDER BY
   c.date_time DESC;

I want to remove duplicated items. So I changed the SQL script to this: 

SELECT
   t.subject, c.parent_id, c.date_time 
FROM
   tbl_tickets_contents c
JOIN
   tbl_tickets t
ON
   t.id = c.parent_id
GROUP BY
   c.parent_id
ORDER BY
   c.date_time DESC;

I assumed this change will give me this result:

enter image description here

But the output was:

enter image description here

It seems GROUP BY changes ORDER BY sorting order. I wanted to have the records with newer date_time, but after this change I have records with older date_time!

How can I solve this problem? What is my mistake?

double-beep
  • 5,031
  • 17
  • 33
  • 41
Mohammad Saberi
  • 12,864
  • 27
  • 75
  • 127
  • Try look this http://stackoverflow.com/questions/5657446/mysql-query-max-group-by – Ella S. Dec 20 '15 at 08:34
  • @EllaSvetlaya, it did not give me what I needed. Result changed after applying `MAX()`, but only `date_time` for each result changed. Whereas I must see result in this order: `parent_id 2, parent_id 3, parent_id 1` regarding to stored `date_time` values. – Mohammad Saberi Dec 20 '15 at 08:48
  • Bro you didn't give the table structure.So it will better to give answer. – Uttam Kumar Roy Dec 20 '15 at 09:02
  • I think you need group by subject, because what if table have more tests in one parent? – Ella S. Dec 20 '15 at 11:01

1 Answers1

0

I modified your code and its working for me.

I change c.date_time to MAX(c.date_time) as date_time so you get latest date time.

And change order by c.date_time to date_time

SELECT
   t.subject, 
   c.parent_id, 
   MAX(c.date_time) as date_time 
FROM
   tbl_tickets_contents c
JOIN
   tbl_tickets t
ON
   t.id = c.parent_id
GROUP BY
   c.parent_id
ORDER BY
   date_time DESC;

SQL Fiddle Example

Uttam Kumar Roy
  • 2,060
  • 4
  • 23
  • 29