Question 420272 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is there a way to prevent ROW_NUMBER's ORDER BY from having to repeat the same thing?

I currently have this SQL:

select ROW_NUMBER()OVER(order by priority DESC NULLS LAST
                               , created_at DESC) as index
     , id
     , title
     , ago(created_at)
     , priority
     , user_id 
from post 
order by priority DESC NULLS LAST
       , created_at DESC;

As you can see, I need to have ROW_NUMBER() and that needs the order by priority DESC NULLS LAST, created_at DESC) as index, id, title, ago(created_at.

This is causing me to repeat the same long order by priority DESC NULLS LAST, created_at DESC twice.

If I need to have even more columns in the order by, that will make it even longer.

Is there a way to prevent this repetition? I tried using an alias after the ORDER BY but that doesn’t seem to be supported.

Tags:

3

Answers


  1. 0 
    SELECT 
  ROW_NUMBER() OVER (ORDER BY priority DESC NULLS LAST, created_at DESC) as index,
  id,
  title,
  ago(created_at),
  priority,
  user_id
FROM post
ORDER BY index;
    Login or Signup to reply.
  2. 0

    Just order by the first column:

    SELECT ROW_NUMBER() OVER (ORDER BY priority DESC NULLS LAST, created_at DESC) AS index
     , id
     , title
     , ago(created_at)
     , priority
     , user_id
FROM post
ORDER BY 1 ASC; -- 1 = first column

    See also the manual

    Login or Signup to reply.
  3. 0

    If you’re looking to re-use the window definition, you can define it separately using window w1 as (order by..), then row_number() over (..) becomes row_number()over w1 and you can reference the same window definition from any other window function. You can also base new window definitions on the one you already have.

    demo at db<>fiddle

    select row_number()over w1 as index
     , sum(id)over w1 as sum1
     , sum(id)over(w1 rows between 1 preceding
                               and 0 following)as sum2
     , id
     , title
     , age(created_at)
     , priority
     , user_id 
from post 
window w1 as (order by priority DESC NULLS LAST
                     , created_at DESC)
--order by index
--order by 1
order by row_number()over w1
    index sum1 sum2 id title age priority user_id
    1 1 1 1 title1 23:54:27.360735 4 2
    2 3 3 2 title2 -00:05:32.639265 3 3
    3 6 5 3 title3 -1 days -00:05:32.639265 2 4
    4 10 7 4 title4 -2 days -00:05:32.639265 1 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search