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
How to Combine Two Completely Different SQL Queries into One Result
How to Select All the Columns of a Table Except One Column
How to Use SQL Like Condition With Multiple Values in Postgresql
How to Include Results of SQL Count If Count=0
Remove Decimal Values Using SQL Query
Regex to Filter for Numers With and Without Dots
How to Sum Up Time Field in SQL Server
Sql 0 Results for 'Not In' and 'In' When Row Does Exist
Sql Query to Select Million Records Quickly
How to Select Three Table With Same Column Name But Different Values
Splitting Date into 2 Columns (Date + Time) in SQL
A SQL Query to Get All the Records Where 5 Columns Are Same But Only One Column Is Different
Get the Number of Digits After the Decimal Point of a Float (With or Without Decimal Part)
How to Remove Line Feed Characters When Selecting Data from SQL Server
Create a Query That Will Display Employee Name and Department Number
How to Compare Timestamp Dates With Date-Only Parameter in MySQL
How to Find Multiple Occurrence of Particular String and Fetch Value in SQL Server
Mysql Error 1449: the User Specified as a Definer Does Not Exist