Select Row number in postgres

sql postgresql window-functions

How to select row number in postgres.

I tried this:

select
    row_number() over (ORDER BY cgcode_odc_mapping_id)as rownum,
    cgcode_odc_mapping_id
  from access_odc.access_odc_mapping_tb
  order by cgcode_odc_mapping_id

and got this error:

ERROR: syntax error at or near "over"
LINE 1: select row_number() over (ORDER BY cgcode_odc_mapping_id)as

I have checked these pages : How to show row numbers in PostgreSQL query?

This is my query:

 select row_number() over (ORDER BY cgcode_odc_mapping_id)as rownum,cgcode_odc_mapping_id from access_odc.access_odc_mapping_tb order by cgcode_odc_mapping_id

this is the error:

ERROR: syntax error at or near "over" LINE 1: select row_number() over (ORDER BY cgcode_odc_mapping_id)as

Not Working doesn't tell us anything that we can help with. Please could you give error messages and/or any other relevant information. Also, please specify the version of PostgreSQL that you are using.

possible duplicate of How to show row numbers in PostgreSQL query?

At a guess, it isn't working because you're trying to use window functions on an old version of PostgreSQL that doesn't support them.

There is no PostgreSQL version 1.8.4.

Please post the output of select version() - there is no (and never was) a version 1.8

vyegorov

SELECT tab.*,
    row_number() OVER () as rnum
  FROM tab;

Here's the relevant section in the docs.

P.S. This, in fact, fully matches the answer in the referenced question.

You should also specify an order in OVER clause: OVER (ORDER BY id). Otherwise the order is not guaranteed.

@pumbo Appears row_number() returns "the row number of the resultset" (i.e. always 1 2 3 4 ... if you specify over ()) however if you have an outer query re-arrange result ordering of course ref: stackoverflow.com/a/3397149/32453 comments

In my case, it came in reverse order, even though there was no outer query; the explicit order by solved the problem.

Select Row number in postgres

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US