curiosityLynx, 11 months ago

My naive guess to how Reddit does it would be that they do two SQL commands that look something like this

WITH CTE AS (
    SELECT  *
           ,row_number() OVER(PARTITION BY magazine ORDER BY heat) AS row_num
    FROM    posts
)
SELECT   *
FROM    cte
WHERE   row_num = 1
ORDER BY heat;

followed by

SELECT *
FROM posts
WHERE id<> $IDs_of_previous_request
ORDER BY heat;