Getting the last row using postgres row_number() window function

Question

I'm learning postgres windows functions and I'm having a little trouble converting this SQL query.

Right now, my query returns the first row in the result of the window function, I want to make it return the last row. I can't use a simple integer like I did with the "first result" because each result has a differing number of rows.

Here's the query:

select * from (select *, row_number() over (partition by user_id) as row_number from photos) as rows where row_number = 1 and deleted = false

I want to be able to specify row_number = last_row() or something similar. Is that possible?

Answer 1

If this is a case where you have a table with multiple photos for each user and you only want the most recent one, then something like this would work. I'm assuming there is a date column of some type telling you when the photo was taken, and for the sake of argument I've named it photo_date.

with all_data as (
  select
    *, max (photo_date) over (partition by user_id) as max_date
  from photos
  where deleted = false
)
select *
from all_data
where
  photo_date = max_date

This uses the max analytic function which for large datasets may work better than row_number because it should be worst case O(n).

On the flip side, if you have two records with the exact same photo date, which also happens to be the most recent, then it is conceivable that this logic would backfire because it would bring back both records. Which one is best really comes down to knowing your data, but for what it's worth consider this an alternative.

Answer 2

This way you can get the last row. Order by is added as suggested by @a_horse_with_no_name in a comment.

select * from 
(select *, row_number() over (partition by user_id) as row_number 
from photos order by row_number desc
)as rows 
where row_number = 1 
and deleted = false