So I was working on a project about transportation system. There are buses reporting to the server. All buses insert new rows into a table called Events every 2 sec, so it is a large table. 

Each bus has a unique busID. I want to get a table which contains all buses but only their latest report.

Here are things I tried:

* Firstly I think I could `ORDER BY time DESC LIMIT 20` It turns out that it is sorting the entire table first then doing the LIMIT thing second... which actually make sense, how else could it sort?
* So I was googling and found out that its much faster to sort with the index. So i did `ORDER BY id DESC LIMIT 20;` It gave me the latest 20 entries pretty fast.
* However I don't really need the latest 20 entries instead that I need the latest entry from all buses. So I was thinking about combining `GROUP BY bus` with `ORDER BY id` somehow but didn't really figure that out...
* Next I read about another post on this site about speeding things up when you only need the max value of a column in each group. So finally I came up with `SELECT driver,busID,route,timestamp,MAX(id) FROM Events GROUP BY bus` However it seems like using MAX(id) does not really help...
* And I think about first using `ORDER BY id LIMIT (some number)` to make a sub table, then find the newest entry of each bus within the sub table. But a problem is that, the tablet on the bus which is sending report might accidentally go offline thus unable to insert new rows. So I don't really know how large should I make the sub table so that it contains at least the latest entry of each bus...

So I am kinda running out of ideas... I am still a noob in mySQL, so maybe there are other better functions to use? Or maybe I am complexing things? I though it wouldn't be so hard to do at the begin ...

Any advice would be greatly appreciated.

---

I also read about this [Retrieving the last record in each group](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group?rq=1) which is brilliant! But it still takes forever in my case...

---

```
CREATE TABLE `Events` (
  `id` bigint(20) NOT NULL auto_increment,
  `driver` varchar(200) collate utf8_unicode_ci default NULL,
  `bus` varchar(200) collate utf8_unicode_ci default NULL,
  `route` varchar(50) collate utf8_unicode_ci default NULL,
  `time` datetime default NULL,
  `clientTime` datetime default NULL,
  `latitude` decimal(30,20) default NULL,
  `longitude` decimal(30,20) default NULL,
  `accuracy` int(11) default NULL,
  `speed` decimal(30,20) default NULL,
  `heading` decimal(30,20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=66528487 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

```

---

Thank you all for helping me！ But it is time to talk to professor! Maybe I am not supposed to do that hmm...