CODEWITHSUNDEEP
sqlpostgresqlsql-view
Enguias
Enguias

Reputation: 73

Create View with an additional generated column

In my postgres project I have a table users and I want to create a view with only the user_fname, user_lname and user_points ordered by user_points but I also want to create the view with a generated column with the users positions, like a leaderboard.

This is what I tried to do, it will explain better:

create view leaderboard as
select 
position int generated always as identity,
user_fname, 
user_lname,
points
from users
order by points desc

Obviously this didn't work, because I'm trying to create the column there, but how can I do something like this?

Upvotes: 0

Views: 471

Answers (1)

Ftisiot
Ftisiot

Reputation: 1868

basically you want to RANK people based on points

If I recreate and populate the users table with

create table users 
(
user_fname varchar, 
user_lname varchar,
points int
);

insert into users values ('Carlo','Rossi',33);
insert into users values ('Mario','Bianchi',35);
insert into users values ('Giorgia','Verdi',35);
insert into users values ('Maria','Blu',37);

The leaderboard can be created with

create or replace view leaderboard as
select 
RANK() OVER (ORDER BY POINTS DESC) as position,
user_fname, 
user_lname,
points
from users
order by points desc;

That will provide

defaultdb=> select * from leaderboard order by position;
 position | user_fname | user_lname | points 
----------+------------+------------+--------
        1 | Maria      | Blu        |     37
        2 | Mario      | Bianchi    |     35
        2 | Giorgia    | Verdi      |     35
        4 | Carlo      | Rossi      |     33
(4 rows)

Note that Mario Bianchi and Giorgia Verdi are both at the 2nd position since they share the same 35 points

Upvotes: 3

Related Questions