Postgresql Extract Last Row for Each Id

Postgresql extract last row for each id

The most efficient way is to use Postgres' distinct on operator

select distinct on (id) id, date, another_info
from the_table
order by id, date desc;

If you want a solution that works across databases (but is less efficient) you can use a window function:

select id, date, another_info
from (
  select id, date, another_info, 
         row_number() over (partition by id order by date desc) as rn
  from the_table
) t
where rn = 1
order by id;

The solution with a window function is in most cases faster than using a sub-query.

How to select the last record of each ID

You can use a window function called ROW_NUMBER.Here is a solution for you given below. I have also made a demo query in db-fiddle for you. Please check link Demo Code in DB-Fiddle

WITH CTE AS
(SELECT product, user_id,
       ROW_NUMBER() OVER(PARTITION BY user_id order by product desc)
       as RN
FROM Mytable)
SELECT product, user_id FROM CTE WHERE RN=1 ;

Get the last and the next to last row per id from a dataset in Postgres

You can try the below - using row_number()

select * from
(
SELECT *, row_number()
OVER (PARTITION BY the_debt_id ORDER BY the_debt_paid DESC) as rn
FROM my_table
)A where rn<=2

PostgreSQL select only last row from each recursion

Walk the tree for all employees. Reduce recursion to ids, add names in the final query:

with recursive cte as
(
    select
        id, manager_id, 0 as level
    from
        employee
    union all
    select
        c.id, e.manager_id, level+ 1
    from cte c
    join employee e on c.manager_id = e.id and e.manager_id is not null
)
select
    e.first_name as employee, 
    m.first_name as highest_level_manager
from (
    select distinct on(id) *
    from cte
    order by id desc, level desc
    ) c
join employee e on c.id = e.id
left join employee m on c.manager_id = m.id

See live demo in Db<>fiddle.

Select last record from data table for each device in devices table

Since you're using Postgres, you could use window functions to achieve this, like so:

select
    sq.id,
    sq.device_id,
    sq.time,
    sq.data
from (
    select
        data.*,
        row_number() over (partition by data.device_id order by data.time desc) as rnk
    from
        data
) sq
where
    sq.rnk = 1

The row_number() window function first ranks the rows in the data table on the basis of the device_id and time columns, and the outer query then picks the highest-ranked rows.

postgres select last record of each id on join with other table

In your case a GROUP BY all columns from lables and the max tome for every session is one choice

SELECT DISTINCT  l.user_id, l.session_id, l.start_time, l.mode,  MAX(s.timestamp) as end_time
FROM labels l
JOIN session s
 ON l.session_id = s.session_id

WHERE l.mode = '' IS NOT TRUE
  GROUP BY l.user_id, l.session_id,l.start_time, l.mode

---