SQL, GroupBy, Unix timestamps...

[StrangeWill]

So I have a table that looks like this:

 

+-------+---------+-----+-----+
| pageid| time    | text| title |
+-------+---------+-----+-----+
| 1     | 100     | text| old
+-------+---------+-----+-----+
|  2    | 200     | text|  none
+-------+---------+-----+-----+
|  1    | 300     | text| new
+-------+---------+-----+-----+

 

I do a group by pageID, but when I do an order by time, the last updated page is page two.

 

Any ideas on how I can group it, get the title for the first entry, but the time for the most recent? Or should I store the most recent entry time?

 

This is my SQL:

SELECT * FROM `pages`
GROUP BY `pages`.`pageid`
ORDER BY `pages`.`time` DESC

 

What I want returned:

+-------+---------+-----+-----+
| pageid| time    | text| title |
+-------+---------+-----+-----+
| 1     | 300     | text| old
+-------+---------+-----+-----+
|  2    | 200     | text| none
+-------+---------+-----+-----+

 

What I get returned:

+-------+---------+-----+-----+
| pageid| time    | text| title |
+-------+---------+-----+-----+
| 1     | 100     | text| old
+-------+---------+-----+-----+
|  2    | 200     | text| none
+-------+---------+-----+-----+

[Barand]

try

SELECT pageid, MAX(time) as maxtime, text, title 
FROM pages
GROUP BY pageid
ORDER BY maxtime DESC