StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POrow_number() with an unspecified window `row_number() OVER ()`
text
Body
copied!<p>I'm building a paginated scoreboard using postgres 9.1. </p> <p>There are several criteria that users can sort the scoreboard by, and they can sort by ascending or descending. There is a feature to let users find "their row" across the multiple pages in the scoreboard, and it must reflect the users selected sorting criteria.</p> <p>I am using postgres's row_number function to find their offset into the result set to return the page where the user can find their row.</p> <p>Everything I'm reading about row_number seems to imply that <strong>bad things</strong> happen to people who don't specify an ordering within the row_number window. E.g. <code>row_number() OVER (ORDER BY score_1)</code> is OK, <code>row_number() OVER ()</code> is <strong>bad</strong>. </p> <p>My case is different from the examples I've read about in that I <em>am</em> explicitly ordering my query, I realize the DB engine may not return the results in any particular order if I don't. </p> <p>But I'd like to just specify ordering at the level of the entire query and get the row_number of the results, without having to duplicate my ordering specification with the row_number's window.</p> <p>So this is what I'd like to do, and it "seems to work".</p> <pre><code>SELECT id, row_number() OVER () AS player_position, score_1, score_2, score_3, FROM my_table ORDER BY (score_1 ASC | score_1 DESC | score_2 ASC | score_2 DESC | score_3 ASC | score_3 DESC) </code></pre> <p>Where <code>player_position</code> reflects the players rank in whatever criteria I'm ordering by. </p> <p>But the documentation I've read tells me I should do it like this:</p> <pre><code>SELECT id, row_number() OVER (ORDER BY score_1 ASC) AS player_position, score_1, score_2, score_3, FROM my_table ORDER BY score_1 ASC </code></pre> <p>or </p> <pre><code>SELECT id, row_number() OVER (ORDER BY score_2 DESC) AS player_position, score_1, score_2, score_3, FROM my_table ORDER BY score_2 DESC </code></pre> <p>The real reason that I'd like to avoid redundantly specifying the ordering for the row_number window is to keep my query amenable with the ActiveRecord ORM. I want to have my base scoreboard query, and chain on the ordering.</p> <p>e.g. Ultimately, I want to be able to do this:</p> <pre><code>Players.scoreboard.order('score_1 ASC') Players.scoreboard.order('score_2 DESC') etc... </code></pre> <p>Is it possible?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload