Column for sequence integers based on ORDER BY clause

SQL++
1

Hi team,

I have an ORDER BY clause based on values from my RANDOM() statement. Is there a way to display the whole number (i.e. integers starting at 1) sequencing of these RANDOM() values in order based on the ORDER BY clause?

Here is a super simplified query illustrating the basics of what I’m trying to accomplish:

SELECT sub.random, (INTEGER_STARTING_WITH_ONE()) as integer
FROM (SELECT (RANDOM()) as random
FROM
WHERE
ORDER BY random) sub
ORDER BY integer

and here is a visual of what I’m looking to kick out of my query:

integer random
1 lowest RANDOM() value
2 second lowest RANDOM() value
3 third lowest RANDOM() value
4 fourth lowest RANDOM() value
5 fifth lowest RANDOM() value

(etc. with no # cap until all data delivered)

Any ideas?

(Note: I do not have the option of creating an INDEX to help with this query)

Thank you all for helping me brainstorm!

2

PRE 6.50

SELECT du.r, UNNEST_POS(du) AS rnumber
FROM 1 AS dummy UNNEST (SELECT RANDOM() AS r FROM WHERE ORDER BY random) AS du;

6.50+ EE version

SELECT d.r, ROW_NUMBER() OVER ( ORDER BY d.r) AS rnumber
FROM (SELECT RANDOM() AS r FROM ... WHERE ...) AS d

https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/windowfun.html#fn-window-row-number

1 Like
3

Thank you so much! I so appreciate your help and for attaching that Couchbase webpage! 5 stars :blush: