Select Row number in postgres

Question

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. — MatBailie
possible duplicate of How to show row numbers in PostgreSQL query? — vyegorov
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. — Craig Ringer
Please post the output of select version() - there is no (and never was) a version 1.8 — a_horse_with_no_name

vyegorov vyegorov · Accepted Answer · 2012-08-14T12:29:26

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.

Select Row number in postgres

1 Answers