Get Last Record of a Table in Postgres

Get last record of a table in Postgres

If under "last record" you mean the record which has the latest timestamp value, then try this:

my_query = client.query("
  SELECT TIMESTAMP,
    value,
    card
  FROM my_table
  ORDER BY TIMESTAMP DESC
  LIMIT 1
");

Postgresql - How to get value from last record of each month

Assuming that you need a month average and a value for the max week not the max value per month

SELECT year, month, category, avg_val, value max_week_val
  FROM (
    SELECT *,
           AVG(value) OVER (PARTITION BY year, month, category) avg_val,
           ROW_NUMBER() OVER (PARTITION BY year, month, category ORDER BY week DESC) rn
      FROM view1
  ) q
 WHERE rn = 1
 ORDER BY year, month, category

or more verbose version without window functions

SELECT q.year, q.month, q.category, q.avg_val, v.value max_week_val
  FROM (
    SELECT year, month, category, avg(value) avg_val, MAX(week) max_week
      FROM view1
     GROUP BY year, month, category
  ) q JOIN view1 v
    ON q.year = v.year
   AND q.month = v.month
   AND q.category = v.category
   AND q.max_week = v.week
 ORDER BY year, month, category

Here is a dbfiddle demo for both queries

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.

PostgreSQL - Get first and last record of relation with one query

Something like this:

select m.user_id, 
       f.name as first_survey, 
       f.created_at as first_survey_created,
       l.name as last_survey, 
       l.created_at last_survey_created
from (
    select user_id, 
           min(created_at) as first_created, 
           max(created_at) as last_created
    from survey_results 
    group by user_id
) m
  join survey_results f on f.user_id = m.user_id and f.created_at = m.first_created
  join survey_results l on l.user_id = m.user_id and l.created_at = m.last_created;

This will not work however, if the first and last survey have the same created_at value

PostgreSQL Latest Record w/o id nor date

SQL tables represent unordered sets and the result sets too. You cannot guarantee your data without specify ORDER BY.

And :

I have a foreign table without id nor date

There is no other way to workaround without this to specify what you need.

My only access to that table is select only

If you only get just Select privilege you should tell your DBA you cannot give the data with 100% guarantee if that is the last data inserted from that user.

can we get totalcount and last record from postgresql

This can be done using window functions

select *
from (
   select m.*, 
          row_number() over (order by createddate desc) as rn,
          count(*) over () as total_count
   from music
) t
where rn = 1;

Another option would be to use a scalar sub-query and combine it with a limit clause:

select *, 
       (select count(*) from order_test.orders) as total_count
from music
order by createddate desc
limit 1;

Depending on the indexes, your memory configuration and the table definition might be faster then the two window functions.

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.

PostgreSQL:How get last rows from a select query

You have two options according to your need i.e,

   select * from data order by value desc limit 2

Or

LIMIT and OFFSET

if you want the 4th and 5th row just offset the first 3 so that the 4th row becomes the start of our set and you can specify a limit to say that you only want 2 rows from that.

   select * from data offset 3 limit 2;
   /* The order of LIMIT and OFFSET does not matter. This gives the same result */
   select * from data limit 2 offset 3;

---

Related Topics