Marko Stojanovic Marko Stojanovic - 2 months ago 6
SQL Question

Sort by descending in a subquery?

I have this SQL query that counts all

Votes
across all
PollOptions
that belong to all existing
Polls
. This works fine, but now I want to implement pagination, which returns latest
Polls
first. I tried to do it like this:

SELECT offset_polls.id AS pollId, offset_polls.title, poll_options.id AS pollOptionId, text, vote_count
FROM (
SELECT * FROM polls ORDER BY id DESC LIMIT 10 OFFSET 0
) as offset_polls
JOIN poll_options ON poll_options.poll_id = offset_polls.id
LEFT OUTER JOIN (
SELECT poll_option_id, COUNT(poll_option_id) as vote_count
FROM votes
GROUP BY poll_option_id
) as votesCount ON poll_options.id = votesCount.poll_option_id
ORDER BY offset_polls.id desc


The problem is that the first subquery ignores ORDER BY - yes I know it's SQL standard to do that. The way I did it, no rows are returned. If I don't use
ORDER BY
in the first subquery, the entire query works as 'intented', but
Polls
are, of course, returned from oldest to newest. How can I solve this in SQL? I could get all rows and do pagination on them afterwards, but I'm pretty sure that would be inefficient - (working in Node.js).

EDIT

The above query works as intended. The 'bug' was in my database seeding files.

Answer

I checked your sample data and I don't see any issue with the behavior of postgresql. What happens is that when you run the query

SELECT * FROM polls ORDER BY id DESC LIMIT 10 OFFSET 0

it returns all polls with the following ids: 20,19,18,...,11. Then, you join those rows with poll_options and you get nothing because there are no rows with a poll_id in that range:

# SELECT * FROM poll_options WHERE poll_id BETWEEN 11 AND 20;
 id | text | poll_id 
----+------+---------
(0 rows)

If you remove the ORDER BY clause from the offset_polls subquery, then it will include 10 "random" rows for which there might be some poll options, which is why the query returns some rows. So ORDER BY in this case in not ignored and behaves exactly as you would expect that it would.

Comments