CODEWITHSUNDEEP
mysql
Surendra Suthar
Surendra Suthar

Reputation: 370

MySql Query Order BY datetime and Group By Id issue

Having issue with this query

there is 3 rows in comments table with different date-times, I want customer list with last comment created_date / updated_date. but didn't getting last commented customer with group by customer

SELECT * FROM(
SELECT MAX(comments.`date_updated`), customer.id AS vid, comments.`date_updated` AS dts, comments.`id` AS comments_id, comments.* FROM customer
INNER JOIN comments ON comments.`customer_id` = customer.`id`
 WHERE customer.`id` IN ('')
) AS v
GROUP BY v.`vid` LIMIT 0,50

Upvotes: 0

Views: 50

Answers (2)

Haq Nawaz
Haq Nawaz

Reputation: 482

You forgot order by clause 

SELECT * FROM(
SELECT MAX(comments.`date_updated`), customer.id AS vid, comments.`date_updated` AS dts, comments.`id` AS comments_id, comments.* FROM customer
INNER JOIN comments ON comments.`customer_id` = customer.`id`
 WHERE customer.`id` IN ('')
) AS v
GROUP BY v.`vid` LIMIT 0,50
ORDER BY created_date desc;

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You use a self join to comments table and filter row for each customer with latest date_updated

SELECT c.id AS vid, co.`date_updated` AS dts, co.`id` AS comments_id, co.*
FROM customer c
INNER JOIN comments co ON co.`customer_id` = c.`id`
LEFT JOIN comments co1 ON co.`customer_id` = co1.`customer_id` AND co.date_updated < co1.date_updated
WHERE co1.customer_id IS NULL  AND c.`id` IN ('')

Or with inner join

SELECT c.id AS vid, co.`date_updated` AS dts, co.`id` AS comments_id, co.*
FROM customer c
INNER JOIN comments co ON co.`customer_id` = c.`id`
INNER JOIN (
    SELECT customer_id, MAX(date_updated) date_updated
    FROM comments
    GROUP BY customer_id
) co1 ON co.customer_id = co1.customer_id AND co.date_updated =  co1.date_updated
WHERE c.`id` IN ('')

Upvotes: 1

Related Questions