# How to query latest record for each record type in data table inside canvas **URL:** \ **Category:** Kibana\ **Created:** [February 22, 2021, 10:22pm UTC](https://discuss.elastic.co/t/how-to-query-latest-record-for-each-record-type-in-data-table-inside-canvas/265132 "2021-02-22T22:22:15Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![tangkalo](https://avatars.discourse-cdn.com/v4/letter/t/cdc98d/32.png) [@tangkalo](https://discuss.elastic.co/u/tangkalo)\ **Post date:** [February 22, 2021, 10:22pm UTC](https://discuss.elastic.co/t/how-to-query-latest-record-for-each-record-type-in-data-table-inside-canvas/265132/1 "2021-02-22T22:22:15Z")
Hi, I have been struggling with what can or cannot be done in Kibana. One thing that we need is to query records from many data sources and be able to display the latest record for each data source. I googled around and found this similar use case in stackoverflow but unfortunately it wasn't answered. > I tried to see if I can use Elasticsearch Query DSL syntax but it doesn't appear I can use it in any elements created off the workpad(please correct me if I am wrong). Any information or suggestion will be highly appreciated. Thanks a lot. ---
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [February 23, 2021, 5:49am UTC](https://discuss.elastic.co/t/how-to-query-latest-record-for-each-record-type-in-data-table-inside-canvas/265132/2 "2021-02-23T05:49:40Z")
It will help if you provide sample data and the required outcome. Generally speaking, without knowing too much about your issue. ```auto Select ID from my_index Group by ID order by timestamp desc ``` ---
**Author:** ![tangkalo](https://avatars.discourse-cdn.com/v4/letter/t/cdc98d/32.png) [@tangkalo](https://discuss.elastic.co/u/tangkalo)\ **Post date:** [February 23, 2021, 2:14pm UTC](https://discuss.elastic.co/t/how-to-query-latest-record-for-each-record-type-in-data-table-inside-canvas/265132/3 "2021-02-23T14:14:44Z")
For example, if I have events coming in like the following: - ## | datasource | timestamp | status | site1 | 8:00 AM | ok | site2 | 8:02 AM | ok | site3 | 8:04 AM | warn | site1 | 8:05 AM | error | site4 | 8:06 AM | error | site3 | 8:07 AM | ok | site2 | 8:08 AM | warn | site5 | 8:09 AM | ok Then, I want to display the latest status for each site order by site name:- ## | datasource | timestamp | status | site1 | 8:05 AM | error | site2 | 8:08 AM | warn | site3 | 8:07 AM | ok | site4 | 8:06 AM | error | site5 | 8:09 AM | ok How would I use Kibana SQL to do that? As for the suggested sql, I think all fields in select list need to be in the group by clause except the ones with aggregation, so timestamp needs to be a group-by field or one aggregated(e.g. max(timestamp) ). Thanks for the response. ---
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [February 25, 2021, 4:49am UTC](https://discuss.elastic.co/t/how-to-query-latest-record-for-each-record-type-in-data-table-inside-canvas/265132/4 "2021-02-25T04:49:31Z")
@tangkalo I have created a data table in Kibana and then imported it into Canvas. I think this is the easiest way to achieve your results. ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/5/b546885b5aea897ddfb55322d89a71ef7b26e927.png) In canvas ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/1/a1aee45eee9345e4dd439045a554d85002644474.png) ---
**Author:** ![tangkalo](https://avatars.discourse-cdn.com/v4/letter/t/cdc98d/32.png) [@tangkalo](https://discuss.elastic.co/u/tangkalo)\ **Post date:** [March 7, 2021, 8:51pm UTC](https://discuss.elastic.co/t/how-to-query-latest-record-for-each-record-type-in-data-table-inside-canvas/265132/5 "2021-03-07T20:51:29Z")
Thanks for the suggestion. I ended up working around it by sorting by timestamp first. Once it's in the data table, I sort by the status field. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 4, 2021, 8:52pm UTC](https://discuss.elastic.co/t/how-to-query-latest-record-for-each-record-type-in-data-table-inside-canvas/265132/6 "2021-04-04T20:52:25Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.