CODEWITHSUNDEEP
sqlpostgresqllimitsql-max
Shashi Preetham
Shashi Preetham

Reputation: 23

OFFSET and LIMIT are not working MAX in POSTGRES

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 !!

Upvotes: 1

Views: 2577

Answers (1)

forpas
forpas

Reputation: 164139

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

Upvotes: 2

Related Questions