sqlite - How to make a query for getting the specific rows with the latest time column value -

below sample data, host:value pair latest time.

+------+-------+-------+ | host | value | time  | +------+-------+-------+ |    |  100  | 13:40 | |    |  150  | 13:00 | |    |  222  | 13:23 |  |  b   |  210  | 13:55 | |  b   |  300  | 13:44 | +------+-------+-------+ 

wanted rows latest time value each host column value. result should like: 150 13:40 b 210 13:55

i think there several analytical function achieve requirement in oracle i'm not sure can in sqlite.

can let me know how can make query?

here ansi-compliant way of performing query should run on all versions of sqlite. potentially shorter solution see answer @cl.

select t1.host || '-' || t1.value || '-' || t1.time hostvaluetime table t1 inner join (     select host, max(time) maxtime     table     group host ) t2 on t1.host = t2.host , t1.time = t2.maxtime order t1.host desc 

output:

+---------------+ | hostvaluetime | +---------------+ |  a-100-13:50  | |  b-210-13:55  | +---------------+