Odif Yltsaeb Odif Yltsaeb - 4 years ago 123
SQL Question

Finding if current row is last row to be selected from database

I am selecting list of periods from database. If current row is first row then the period starts with date and I can find the interval between period start like this:

CASE WHEN row_number() OVER(ORDER BY r.created_at ASC) = 1 THEN r.created_at - r.created_at::date ELSE NULL END AS period
FROM mytable r

How can I do the same to last row? To find the time between the r.created_at of last row and midnight of its date.

I am aware of
functions in PostgreSQL (https://wiki.postgresql.org/wiki/First/last_(aggregate)), but they are aggregate functions and do not help in this case.

This question has 2 great answers. Neither of them help in my case, as this single line i presented as part of my question is part of bigger query, put together programmatically and using the solutions offered would force me to alter alot of code, which i am not willing to do at this point. Should the scaling problems hit - then i will certainly reconsider.

Answer Source

This might be faster than window functions:

with r as (
        min(created_at) as min_created_at,
        max(created_at) as max_created_at
    from mytable
    case when (select min_created_at from r) = created_at
    then created_at - created_at::date else null
    end as period_min,
    case when (select max_created_at from r) = created_at
    then created_at - created_at::date else null
    end as period_max
from mytable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download