Window functions: is PARTITION BY the right way to walk through the table?

Question

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?

Answer 1

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