CODEWITHSUNDEEP
mysqlsqldatabase
KillABug
KillABug

Reputation: 1414

Getting latest entries from the revision table in mysql

I have a revision database table with multiple revisions for pages(pageId) under a website(webId).I want to write a query to get only the latest revisions of a website.For example the table below shows two files with id 1 and 2,now i want to get the revision number 101 and 104 as they are latest for their respective pages.I am using mysql

+----+---------+-------------+---------------------+-------+---------------------+
| id | userId  | webId       |revisionCont         | pageId| dateUpdated         |
+----+---------+-------------+---------------------+-------+---------------------+
| 100|       1 |           2 | some text           | 1     | 2014-01-07 08:00:00 |
| 101|       1 |           2 | some text           | 1     | 2014-01-07 08:01:00 |
| 103|       2 |           2 | some text           | 2     | 2014-01-07 08:15:32 |
| 104|       2 |           2 | some text           | 2     | 2014-01-07 09:10:32 |
+----+---------+-------------+---------------------+-------+---------------------+

I am not able to figure out how can I do it?Can someone guide me over it?

Upvotes: 1

Views: 582

Answers (4)

deW1
deW1

Reputation: 5660

SELECT MAX(id) id FROM revision GROUP BY pageId

Should do it.

Upvotes: 3

echo_Me
echo_Me

Reputation: 37233

try this

 SELECT max(id) id FROM revision 
 GROUP BY pageId

DEMO HERE

output:

ID
104
101

EDIT: if you need to filrter or order the output then just add ORDER BY dateUpdated DESC

Upvotes: 4

eKek0
eKek0

Reputation: 23289

To obtain the complete row do:

SELECT * FROM revision WHERE Id IN (
  SELECT MAX(id) FROM revision GROUP BY pageId ORDER BY dateUpdated DESC
)

Upvotes: 3

Legionar
Legionar

Reputation: 7597

Here is query example:

SELECT id FROM revision GROUP BY pageId ORDER BY dateUpdated DESC

It will get all records IDs (101 and 104), for each pageId, ordered by dateUpdated (descended)

Upvotes: 2

Related Questions