Select with variable LIMIT value

Question

I have a comments table. Every single comment could be a response to another comment.

Table structure:

CREATE TABLE comments
(
    id integer NOT NULL,
    content text,
    root integer NULL,
    date_added timestamp without time zone DEFAULT now()
)

I need to select the newest 10 threads with their responses, so in result I may have for example 10 threads and 8 responses. I'm not sure hot to do that. I've tried:

SELECT content FROM comments 
ORDER BY date_added DESC 
LIMIT 10 + (SELECT COUNT(*) FROM COMMENTS WHERE root IS NOT NULL)

But this has no proper effect.

simple example of input data and desired output data could help. Do you need all responses down to the tree? — roman
– roman, Commented Nov 13, 2013 at 15:22

roman · Accepted Answer · 2013-11-13 18:22:40Z

1

not sure is that what you want, but looks like recursive cte could help you:

with recursive cte as (
    -- anchor of the query - last 10 threads
    select c.id, c.content 
    from comments as c
    where c.root is null -- top level threads
    order by c.date_added desc
    limit 10

    union all

    -- recursively get all children
    select c.id, c.content
    from comments as c
        inner join cte as cr on cr.id = c.root
)
select
    content
from cte

answered Nov 13, 2013 at 18:22

roman

118k30 gold badges205 silver badges209 bronze badges

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

1 Comment

webrama.pl Over a year ago

This is pretty what I need. As far I know using LIMIT with UNION requires brackets added at start and end of queries.

Collectives™ on Stack Overflow

Select with variable LIMIT value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related