131

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

Community
  • 1
  • 1
Maverick
  • 1,952
  • 4
  • 21
  • 33
  • 3
    `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 Aug 14 '12 at 12:27
  • 1
    possible duplicate of [How to show row numbers in PostgreSQL query?](http://stackoverflow.com/questions/3397121/how-to-show-row-numbers-in-postgresql-query) – vyegorov Aug 14 '12 at 12:31
  • 1
    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 Aug 14 '12 at 12:38
  • Sorry for not mentioning the error. 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 and this is the error: ERROR: syntax error at or near "over" LINE 1: select row_number() over (ORDER BY cgcode_odc_mapping_id)as . – Maverick Aug 14 '12 at 12:48
  • Please don't hide basic information in comments, edit your question instead. As it stands your question is useless. – Erwin Brandstetter Aug 14 '12 at 19:27
  • 2
    There is no PostgreSQL version 1.8.4. – kgrittn Aug 15 '12 at 12:15
  • @Maverick: I incorporated information you put into a comment and an answer into the question, as is the proper way to do this sort of thing. Please use the "edit" link under your question for this sort of thing. Also, please click "edit" now and look at what I did to format the information for readability. – kgrittn Aug 15 '12 at 12:24
  • 1
    Please post the output of `select version()` - there is no (and never was) a version 1.8 –  Sep 01 '13 at 21:27
  • Does this answer your question? [How to show row numbers in PostgreSQL query?](https://stackoverflow.com/questions/3397121/how-to-show-row-numbers-in-postgresql-query) – Mike Feb 21 '20 at 11:13

1 Answers1

246
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.

vyegorov
  • 21,787
  • 7
  • 59
  • 73
  • 39
    You should also specify an order in `OVER` clause: `OVER (ORDER BY id)`. Otherwise the order is not guaranteed. – pumbo Jun 14 '16 at 04:30
  • 5
    @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: http://stackoverflow.com/a/3397149/32453 comments – rogerdpack Jul 08 '16 at 15:54
  • 2
    In my case, it came in reverse order, even though there was no outer query; the explicit `order by` solved the problem. – Haroldo_OK Nov 10 '21 at 14:15