Using dynamically limit in postgresql

Question

I want to use

select * from table limit x;

This x can change dynamically. I don't want to use number. There are a solution for limit that use parameter?

Where does this limit come from? Is it calculated within postgresql? — fafl
– fafl, Commented Nov 29, 2016 at 12:00
Can you give an exmple? Why do you store limits inside a column? How are they calculated? — fafl
– fafl, Commented Nov 29, 2016 at 12:02
I want to call table according to months. If its 31,30,29,28 etc. Program calculate months and I want to use it as limit. there is a way in postgresql for this? — nachtnegall
– nachtnegall, Commented Nov 29, 2016 at 12:06

Richard Huxton · Accepted Answer · 2016-11-29 12:05:53Z

2

Sure you can. Never needed to myself but:

SELECT * FROM some_table LIMIT (SELECT a_limit FROM other_table);

Obviously that sub-query should only return one row.

answered Nov 29, 2016 at 12:05

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richard Huxton Over a year ago

Yes it does. I ran it before posting.

raphael · Accepted Answer · 2019-11-01 14:09:13Z

0

To further expand on Richard Huxton's answer, this could be used in a LATERAL sub-query, for example to dynamically limit a K-Nearest Neighbours query:

SELECT loc_id, num_neighbours, neighbour_id
FROM locations,
LATERAL (SELECT neighbour_id 
         FROM neighbours 
         ORDER BY ST_distance(locations.geom, neighbours.geom) 
         LIMIT num_neighbours) knn

answered Nov 1, 2019 at 14:09

raphael

2,8795 gold badges30 silver badges57 bronze badges

Collectives™ on Stack Overflow

Using dynamically limit in postgresql

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related