How to generate a numbered index for results of SELECT query in PostgreSQL?

Question

I have a database related to a 2 player digital board game. I have created a query that, when given a user id, will return a list of played games records. I want the results to be accompanied by a numbered index, but I am unable to find a way to generate one.

There are 4 tables in my database: users, games, layouts, and users_games. The first 3 tables all have an id column which is the primary key and used as a foreign key in tables which reference it. The users_games table has no primary key.

Here is my query so far:

SELECT DISTINCT ON (games.id) row_number() over() AS num,
 (SELECT nickname FROM users INNER JOIN users_games ON users.id = users_games.user_id WHERE id != 'some_user_id' AND game_id = games.id ) AS opponent_name,
 (SELECT score FROM users_games WHERE game_id = games.id and user_id != 'some_user_id') as opponent_score, 
 (SELECT score FROM users_games WHERE game_id = games.id and user_id = 'some_user_id') as player_score,
 (SELECT
  CASE
   WHEN (SELECT score FROM users_games WHERE game_id = games.id and user_id = 'some_user_id') > (SELECT score FROM users_games WHERE game_id = games.id and user_id != 'some_user_id') THEN 'Won'
   WHEN (SELECT score FROM users_games WHERE game_id = games.id and user_id != 'some_user_id') > (SELECT score FROM users_games WHERE game_id = games.id and user_id = 'some_user_id') THEN 'Lost'
   ELSE 'Tie'
   END) as result,
 layouts.name AS layout, date(end_time) AS date
FROM
 users_games
INNER JOIN
 users ON users_games.user_id = users.id
INNER JOIN
 games ON users_games.game_id = games.id
INNER JOIN layouts
 ON games.layout_id = layouts.id 
 WHERE games.id IN (SELECT game_id FROM users_games WHERE user_id = 'some_user_id') 
 ORDER BY games.id asc;

I feel the way I have written it is very inefficient and difficult to read, but that's a different question..

The issue I'm facing is that I can't get a numbered index.

Here are the results of the above query:

 num | opponent_name | opponent_score | player_score | result | layout  |    date    
-----+---------------+----------------+--------------+--------+---------+------------
   1 | Medium AI     |             34 |            3 | Lost   | oldcity | 2021-08-31
   3 | Easy AI       |             21 |           31 | Won    | razeway | 2021-09-01
   5 | Easy AI       |             43 |           22 | Lost   | razeway | 2021-09-01
   7 | Easy AI       |             10 |           32 | Won    | razeway | 2021-09-01
   9 | Medium AI     |             10 |           32 | Won    | razeway | 2021-09-01
  11 | Medium AI     |             34 |           15 | Lost   | razeway | 2021-09-01
  13 | Medium AI     |             56 |           21 | Lost   | oldcity | 2021-09-01
  15 | Easy AI       |             22 |           22 | Tie    | razeway | 2021-09-03

As you can see the num column is skipping a digit on each column. That seems to be because my query is actually generating 2 rows, 1 for each user in each game, which I solve by using DISTINCT ON (games.id) in my query.

I have also tried to use count(*) to generate the num index, or using group by games.id or group by game_id but in each case I get the following error:

ERROR: subquery uses ungrouped column "games.id" from outer query

How can I generate a numeric index, starting at 1 and incrementing by 1 for each row in the results from my query?

Get rid of the DISTINCT ON, then get your query to return the results you want without the numbering. Then wrap the whole thing in SELECT row_number() over (), * from (...)_. (The trailing underscore matters, PG requires subqueries in the FROM list to be named. In this case, we're calling it _). — AdamKG
– AdamKG, Commented Sep 2, 2021 at 20:26
This worked, thank you! If you add it as an answer rather than a comment I can mark it as the accepted answer. Thank you again! — Dylan
– Dylan, Commented Sep 2, 2021 at 20:33

AdamKG · Accepted Answer · 2021-09-02 20:43:01Z

2

Get rid of the DISTINCT ON, then get your query to return the results you want without the numbering. Then, wrap the whole thing in SELECT row_number() over (), * from (...)_. The trailing underscore does matter, Postgres requires subqueries in the FROM list to be named. In this case, we're calling it _.

answered Sep 2, 2021 at 20:43

AdamKG

14.1k3 gold badges41 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to generate a numbered index for results of SELECT query in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related