CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Oto Shavadze
Oto Shavadze

Reputation: 42753

Ranking rows according some number

I have table like this

 id    | name
----------
1      | A
2      | B
5      | C
100    | D
200    | E
201    | F
202    | G

I need ranking rows from 1 to 3 order by id, that is, I need result:

 id    | name | ranking
---------------------------
1      | A    | 1
2      | B    | 2
5      | C    | 3
100    | D    | 1
200    | E    | 2
201    | F    | 3
202    | G    | 1

How to make this?

P.S.

I am trying:

 SELECT  id, name, row_number() OVER( order by id RANGE  BETWEEN 1 AND 3 ) AS ranking FROM t

This gives syntax error.

Upvotes: 0

Views: 62

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

RANGE is actually used for something else:

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

http://www.postgresql.org/docs/current/static/sql-select.html

Try using a modulus instead:

SELECT  id, name, 1 + (row_number() OVER( order by id ) - 1) % 3 AS ranking
FROM t

Upvotes: 1

Related Questions