# SELECT the last three rows in DATA with IN list **URL:** \ **Category:** Elasticsearch\ **Tags:** elastic-stack-sql\ **Created:** [March 3, 2022, 5:43pm UTC](https://discuss.elastic.co/t/select-the-last-three-rows-in-data-with-in-list/298771 "2022-03-03T17:43:07Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![fastxl](https://avatars.discourse-cdn.com/v4/letter/f/85e7bf/32.png) [@fastxl](https://discuss.elastic.co/u/fastxl)\ **Post date:** [March 3, 2022, 5:43pm UTC](https://discuss.elastic.co/t/select-the-last-three-rows-in-data-with-in-list/298771/1 "2022-03-03T17:43:07Z")
Trying to select the last 3 rows of a group. If I run this query ```auto POST /_sql?format=txt { "query": "SELECT \"Samples.AccumulatedTime.@@data\",\"Samples.AccumulatedTime.name\",deviceName,timestamp FROM \"mtconnect-demo\" WHERE \"Samples.AccumulatedTime.name\" IN ('p1TotalOperatingTime', 'p1TotalRunningTime', 'p1TotalCuttingTime') AND deviceName = 'OKUMA.MachiningCenter.MU6300' ORDER BY timestamp DESC" } ``` I get this result which I expect ```auto Samples.AccumulatedTime.@@data|Samples.AccumulatedTime.name| deviceName | timestamp ------------------------------+----------------------------+----------------------------+------------------------ 3949148 |p1TotalCuttingTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:22.255Z 45546100 |p1TotalRunningTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:22.243Z 4973127 |p1TotalOperatingTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:22.229Z 3949146 |p1TotalCuttingTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:20.069Z 45546098 |p1TotalRunningTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:20.043Z 4973125 |p1TotalOperatingTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:20.033Z 3949143 |p1TotalCuttingTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:17.506Z 45546095 |p1TotalRunningTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:17.496Z 4973122 |p1TotalOperatingTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:17.485Z 3949141 |p1TotalCuttingTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:15.259Z 45546092 |p1TotalRunningTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:15.233Z 4973120 |p1TotalOperatingTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:15.222Z ``` So I figure I would do a `TOP 1` to get the top result of each in my list ```auto POST /_sql?format=txt { "query": "SELECT TOP 1 \"Samples.AccumulatedTime.@@data\",\"Samples.AccumulatedTime.name\",deviceName,timestamp FROM \"mtconnect-demo\" WHERE \"Samples.AccumulatedTime.name\" IN ('p1TotalOperatingTime', 'p1TotalRunningTime', 'p1TotalCuttingTime') AND deviceName = 'OKUMA.MachiningCenter.MU6300' ORDER BY timestamp DESC" } ``` But that will return just a single line of ```auto Samples.AccumulatedTime.@@data|Samples.AccumulatedTime.name| deviceName | timestamp ------------------------------+----------------------------+----------------------------+------------------------ 3949148 |p1TotalCuttingTime |OKUMA.MachiningCenter.MU6300|2022-03-03T17:32:22.255Z ``` And not the 3 items in my `IN` list How can I get the `TOP 1` of each row of `p1TotalOperatingTime, p1TotalRunningTime, p1TotalCuttingTime`? ---
**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\ **Post date:** [March 4, 2022, 3:06am UTC](https://discuss.elastic.co/t/select-the-last-three-rows-in-data-with-in-list/298771/2 "2022-03-04T03:06:12Z")
DSL like this: ```auto POST index1/_search { "size": 0, "aggs": { "groupby": { "terms": { "field": "Samples.AccumulatedTime.name", "size": 3 }, "aggs": { "top1": { "top_hits": { "size": 1, "sort": [ { "timestamp": { "order": "desc" } } ] } } } } } } ``` ---
**Author:** ![fastxl](https://avatars.discourse-cdn.com/v4/letter/f/85e7bf/32.png) [@fastxl](https://discuss.elastic.co/u/fastxl)\ **Post date:** [March 4, 2022, 12:38pm UTC](https://discuss.elastic.co/t/select-the-last-three-rows-in-data-with-in-list/298771/3 "2022-03-04T12:38:47Z")
Thanks for the direction but I get this ```auto "error" : { "root_cause" : [ { "type" : "illegal_argument_exception", "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [Samples.AccumulatedTime.name] in order to load field data by uninverting the inverted index. Note that this can use significant memory." } ], "type" : "search_phase_execution_exception", "reason" : "all shards failed", "phase" : "query", "grouped" : true, "failed_shards" : [ { "shard" : 0, "index" : "mtconnect-demo", "node" : "N_i5Gs8zQsWDhwgQZq5Thw", "reason" : { "type" : "illegal_argument_exception", "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [Samples.AccumulatedTime.name] in order to load field data by uninverting the inverted index. Note that this can use significant memory." } } ], "caused_by" : { "type" : "illegal_argument_exception", "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [Samples.AccumulatedTime.name] in order to load field data by uninverting the inverted index. Note that this can use significant memory.", "caused_by" : { "type" : "illegal_argument_exception", "reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [Samples.AccumulatedTime.name] in order to load field data by uninverting the inverted index. Note that this can use significant memory." } } }, "status" : 400 } ``` I am an ELK novice so any help would be great,. ---
**Author:** ![casterQ](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/casterq/32/93257_2.png) [@casterQ](https://discuss.elastic.co/u/casterQ)\ **Post date:** [March 4, 2022, 2:06pm UTC](https://discuss.elastic.co/t/select-the-last-three-rows-in-data-with-in-list/298771/4 "2022-03-04T14:06:32Z")
terms agg must be used with keyword type，so you need set Samples.AccumulatedTime.name as keyword type ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 1, 2022, 2:06pm UTC](https://discuss.elastic.co/t/select-the-last-three-rows-in-data-with-in-list/298771/5 "2022-04-01T14:06:45Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.