# How to select the newest rows in an ordered result set?

**URL:** <https://discuss.elastic.co/t/how-to-select-the-newest-rows-in-an-ordered-result-set/297598>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [February 18, 2022, 10:50am UTC](https://discuss.elastic.co/t/how-to-select-the-newest-rows-in-an-ordered-result-set/297598 "2022-02-18T10:50:44Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![fgjensen](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fgjensen/32/62320_2.png) [@fgjensen](https://discuss.elastic.co/u/fgjensen)\
**Post date:** [February 18, 2022, 10:50am UTC](https://discuss.elastic.co/t/how-to-select-the-newest-rows-in-an-ordered-result-set/297598/1 "2022-02-18T10:50:44Z")

</div>

Hello

I have an annoying problem with Elasticsearch SQL I hope someone can help me with. Elasticsearch is version 7.17.0 and it will soon be upgraded to 8.0.0.

We collect package data from Linux hosts with the auditbeat system module. The module is configured to send the current packages data each 12 hours. I am showing the package names, versions and description for a subset of the packages. The number of packages shown per host is different, but this no problem with an in clause.

The problem appears when a host is updated with new packages then the simple approach with

```auto
and "@timestamp" > NOW() - INTERVAL 12 HOUR

```

would work, because of two rows per package.

My next solution was to query for the timestamp of each package, grouping and ordering. The newest package is then always the first and the oldest the next for a given package name.

The I though I was home free with an outer select after

```auto
where "@timestamp" = MAX("@timestamp)

```

That does not work. I have also tried to CAST to LONG but the error message is the same

```auto
{
  "error" : {
    "root_cause" : [
      {
        "type" : "ql_illegal_argument_exception",
        "reason" : "Line 39:20: Comparisons against fields are not (currently) supported; offender [max(timeconv)] in [==]"
      }
    ],
    "type" : "ql_illegal_argument_exception",
    "reason" : "Line 39:20: Comparisons against fields are not (currently) supported; offender [max(timeconv)] in [==]"
  },
  "status" : 500
}

```

Any suggestions would be appreiciated.

Best regards  
Flemming

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 18, 2022, 10:51am UTC](https://discuss.elastic.co/t/how-to-select-the-newest-rows-in-an-ordered-result-set/297598/2 "2022-03-18T10:51:08Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.