OFFSET and LIMIT are not working MAX in POSTGRES

Question

The Follow query in the PGADMIN is giving me Nothing,

SELECT MAX(time) AS MaxID 
FROM table_name 
where column_name like 'valu_name' 
OFFSET 1000 Limit 1000

Where as if I run the following query it gives me output

SELECT MAX(time) AS MaxID 
FROM table_name 
where column_name like 'valu_name'

But why? Why does the MAX function not work with OFFSET and LIMIT?

What is the right way to do it?

The use case is to get the max time stamp of rows between the 2000 to 3000, that why I am using the OFFSET and LIMIT, the From to To rows may changes !!

That is because that query returns only one value, so it doesn ´t make sense to get the rows from 1000 to 2000 (they don´t exist) — nacho, Aug 28 '21 at 11:20
`SELECT MAX(time) AS MaxID FROM table_name ` returns exactly one row with one column, so obviously `offset 1000` isn't returning anything — , Aug 28 '21 at 11:20
The use case is to get the max time stamp of rows between the 2000 to 3000, that why I am using the OFFSET and LIMIT, the from to To rows may changes !! — Shashi Preetham, Aug 28 '21 at 11:24
@a_horse_with_no_name can you give me a better idea how to do it ? — Shashi Preetham, Aug 28 '21 at 11:30
[Logical query processing](https://stackoverflow.com/questions/32668427/why-do-linq-to-sql-queries-starts-with-the-from-keyword-unlike-regular-sql-que/32668470#32668470) - OFFSET-FETCH is applied last. Second, without explicit `ORDER BY` entire query is underetministic(tables are unordered sets) — Lukasz Szozda, Aug 28 '21 at 11:32
Please note that `like` without wildcards is equivalent to `=`. Note also the remark of @forpas about `order by`. — Stefanov.sm, Aug 28 '21 at 11:34

score 1 · Accepted Answer · answered Aug 28 '21 at 11:31

OFFSET and LIMIT are applied after you get the result for MAX(time), which is only 1 row.

If you want to get the max time of 1000 rows, arbitrary returned from your table since there is no ORDER BY clause, you should use a subquery that returns these rows and then aggregate:

SELECT MAX(time) AS MaxID 
FROM (
  SELECT time
  FROM table_name 
  WHERE column_name like 'valu_name' 
  OFFSET 1000 Limit 1000
) t

OFFSET and LIMIT are not working MAX in POSTGRES

1 Answers1