0 votes
1 view
in SQL by (21k points)

How to select the 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

1 Answer

0 votes
by (37.4k points)

Try this code:

SELECT tab.*,

    row_number() OVER () as rnum

  FROM tab;

For detailed information, you can refer to:

https://www.postgresql.org/docs/current/functions-window.html

...