CODEWITHSUNDEEP
sqlmysql
Cyril
Cyril

Reputation: 1649

Group and select last insert in a table

I've a table to save some file with a simple versionning system like :

ID | FILENAME | CONTENT | DATE
1 | index.php | Content of index.php v1 | timestamp
2 | main.css| Some CSS content | timestamp
3 | index.php | Content of index.php v2 | timestamp

I need to code a SQL request to find in this table the last inserted content of each file.

For this, I tried to group and order by date but with this request :

SELECT * FROM modeles GROUP BY filename ORDER BY date DESC

but it didn't work because the order is done after the group and because the group take the first entry.

If someone here know the solution (this is probably easy but i don't know how to do...).

Thank you.

Upvotes: 0

Views: 1539

Answers (1)

Don
Don

Reputation: 9661

You can use a Subquery

SELECT ID, FILENAME, CONTENT, DATE
FROM TABLE AS Main
WHERE ID = (SELECT MAX(Sub.ID) FROM TABLE AS Sub WHERE Sub.FILENAME = Main.FILENAME)

Optional WHERE if ID is not always the high on the last edited one_

WHERE DATE = (SELECT MAX(Sub.DATE) FROM TABLE AS Sub WHERE Sub.FILENAME = Main.FILENAME)

Upvotes: 1

Related Questions