CODEWITHSUNDEEP
mysql
Paul Denisevich
Paul Denisevich

Reputation: 2414

Get last updated time for all items grouped by category

here is my table of items:

id (int)
category_id (int)
title (varchar 255)
updated_timestamp (int)

Each item has a category_id. Let's say I have this data:

1  |  14  |  item 1  |  1376164492
2  |  11  |  item 2  |  1376164508
3  |  12  |  item 3  |  1376164512
4  |  14  |  item 4  |  1376164532
5  |  12  |  item 5  |  1376164542
6  |  11  |  item 6  |  1376164552

My goal is to get last update timestamp for each category. As a result I need this:

11 = 1376164522 (greatest value for all items with category_id = 11)
12 = 1376164542 (greatest value for all items with category_id = 12)
14 = 1376164532 (greatest value for all items with category_id = 14)

Here is my SQL:

SELECT 
    `category_id`, `updated_timestamp` 
FROM 
    `my_table` 
GROUP BY 
    `category_id` 
ORDER BY 
    `updated_timestamp` 
DESC

But when I run it I have a result like this:

11 = 1376164508 (lowest value for all items with category_id = 11)
12 = 1376164512 (greatest value for all items with category_id = 12)
14 = 1376164492 (greatest value for all items with category_id = 14)

Another words, DESC does not work. What I am doing wrong?

Thank you!

Upvotes: 1

Views: 129

Answers (3)

Phil
Phil

Reputation: 164912

SELECT *
FROM `my_table` a
WHERE NOT EXISTS (
    SELECT 1 FROM `my_table` b
    WHERE b.`category_id` = a.`category_id`
    AND b.`updated_timestamp` > a.`updated_timestamp`
)
ORDER BY `updated_timestamp`

Upvotes: 2

Conrad Lotz
Conrad Lotz

Reputation: 8828

Try the query as follow by adding MAX(updated_timestamp)

SELECT 
    `category_id`, MAX(`updated_timestamp` )
FROM 
    `Table1` 
GROUP BY 
    `category_id` 
ORDER BY 
    `updated_timestamp` 
DESC

SQL FIDDLE DEMO

Upvotes: 2

John Woo
John Woo

Reputation: 263803

This query allows you to get all columns in the table.

SELECT  a.*
FROM    items a
        INNER JOIN
        (
            SELECT  category_id, MAX(updated_timestamp) updated_timestamp
            FROM    items
            GROUP   BY category_id
        ) b ON a.category_id = b.category_id AND
                a.updated_timestamp = b.updated_timestamp

Upvotes: 2

Related Questions