CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
avi
avi

Reputation: 1846

Window functions in PostgreSQL

I have the following table:

id username
1   Jack
2   Will
3   Grace
4   Niv

I want to write a 3 column query which shows username, username before and username after based on dictionary order.

Meaning:

before username  after
       Grace     Jack
Grace  Jack      Niv
Jack   Niv       Will
Nive   Will

I wrote this query:

select lag(username,1) over (partition by username order by username ) as before,
    username,
    lead(username,1) over (partition by username order by username ) as after
from users
order by username

But it doesnt work. It shows me data only in the username column. What am I doing wrong?

Upvotes: 2

Views: 461

Answers (2)

Kiran
Kiran

Reputation: 3315

another way without using window functions

select a.username, 
                  (select min(username) from users b
                    where b.username > a.username) as after,
                   (select max(username) from users c
                   where c.username < a.username) as before
 from users a
order by username;

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You should remove PARTITION BY:

SELECT
    LAG(username, 1) OVER (ORDER BY username) AS before,
    username,
    LEAD(username, 1) OVER (ORDER BY username) AS after
FROM users
ORDER BY username;

DBFiddle Demo

If you have duplicates inusers(username) you could add DISTINCT:

SELECT ...
FROM (SELECT DISTINCT username FROM users) AS sub
ORDER BY ...;

EDIT:

But it doesnt work. It shows me data only in the username column. What am I doing wrong?

When you use partition by you basically divide the rows into groups that share the same values of the PARTITION BY expression. So your window contains only one value. That is the reason why you get NULL (there is no prior/next value).

DBFidde Demo2

DBFiddle Demo3 with duplicates per window

Upvotes: 4

Related Questions