CODEWITHSUNDEEP
mysqlsql
Unapedra
Unapedra

Reputation: 2373

MySQL - Select only the last modified (or NULL value)

I have a MySQL database table with the following structure:

+----+---------+---------+---------------------+
| id | item_id |  name   |      modified       |
+----+---------+---------+---------------------+
|  1 |    1829 | Item 1  | 2013-10-20 20:42:34 |
|  2 |    1737 | Item 2  | 2013-10-25 12:33:01 |
|  3 |    1829 | Item 3  | 2013-10-22 18:47:22 |
|  4 |    1737 | Item 2b | 2013-10-29 21:12:29 |
|  6 |    1829 | Item 3b | NULL                |
+----+---------+---------+---------------------+

What I'd like to do is, in a sole query, get all items, but if repeated only get those newer (modified field is newer) or whose modified field IS NULL.

So, what I'd get of this table would be:

+----+---------+---------+---------------------+
| id | item_id |  name   |      modified       |
+----+---------+---------+---------------------+
|  1 |    1829 | Item 1  | 2013-10-20 20:42:34 |
|  4 |    1737 | Item 2b | 2013-10-29 21:12:29 |
|  6 |    1829 | Item 3b | NULL                |
+----+---------+---------+---------------------+

I could do that with a second select, something like:

SELECT db_items.item_id, db_items.name, db_items.modified  
FROM (SELECT item_id, name, modified 
    FROM db_items 
    ORDER BY modified DESC) db_items 
GROUP BY item_id

But I'd like to know if it's possible without inner SELECTS (as this is only an example and I'd use it in more complicated queries).

Thanks for your time and answers!

Upvotes: 0

Views: 245

Answers (1)

Benten
Benten

Reputation: 1039

Just try

SELECT *
FROM db_items 
GROUP BY item_id
ORDER BY modified DESC

Upvotes: 1

Related Questions