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 ;
Select last record of each month for each ID
With ROW_NUMBER() window function:
SELECT ID, date, data
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID, strftime('%Y%m', date) ORDER BY date DESC) rn
FROM myTable
)
WHERE rn = 1
Get most recent date for each id
select ID, Max(Date) as Date
from yourtable
group by ID
how do I query sql for a latest record date for each user
select t.username, t.date, t.value
from MyTable t
inner join (
select username, max(date) as MaxDate
from MyTable
group by username
) tm on t.username = tm.username and t.date = tm.MaxDate
select rows in sql with latest date for each ID repeated multiple times
This question has been asked before. Please see this question.
Using the accepted answer and adapting it to your problem you get:
SELECT tt.*
FROM myTable tt
INNER JOIN
(SELECT ID, MAX(Date) AS MaxDateTime
FROM myTable
GROUP BY ID) groupedtt
ON tt.ID = groupedtt.ID
AND tt.Date = groupedtt.MaxDateTime
How to get last data for each id/date?
You can do this pretty easily using data.table
syntax...
# Load package
require( data.table )
# Turn 'data.frame' into 'data.table'
dt <- data.table( df )
# Make dates from date/time
dt[ , Date:= as.Date( Tpt_DateTime ) ]
# Get last row of each group
dt[ , .SD[.N] , by = c("Tpt_ID" , "Date") ]
# Tpt_ID Date Tpt_DateTime Value
#1: 1 2013-01-01 2013-01-01 11:17:21 21
#2: 2 2013-01-01 2013-01-01 07:18:32 5
#3: 3 2013-01-01 2013-01-01 08:00:02 1
#4: 1 2013-01-02 2013-01-02 07:10:11 15
#5: 2 2013-02-02 2013-02-02 12:18:32 8
#6: 3 2013-02-03 2013-02-03 14:00:02 3
First we turn your
data-time
data into a date withDate :=
as.Date( Tpt_DateTime )Then we use
.SD
to get a subset of X's data for each group..N
contains the number of row for each group, so.SD[.N]
gives us the last row for each group.Lastly, the
by=c("Tpt_ID" , "Date")
defines the groups.
Find the first AND last date that an ID occured in dataset SQL
Simply do a GROUP BY
. Use MIN()
and MAX()
to get first and last date.
select OrderID,
MIN(SnapShotDate) as FirstDateOccuring,
MAX(SnapShotDate) as LastDateOccuring
from tablename
group by OrderID
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.
Related Topics
Visual Bug When Changing Robinson Projection's Central Meridian with Ggplot2
Puzzled by Xlim/Ylim Behavior in R
Getting the Minimum of the Rows in a Data Frame
Data.Table Joins - Select All Columns in the I Argument
Non-Standard Evaluation and Quasiquotation in Dplyr() Not Working as (Naively) Expected
How to Log Transform the Y-Axis of R Geom_Histogram in the Right Direction
In R Data.Frame, Promote Rownames to Actual Column
Labelling Points with Ggplot2 and Directlabels
Round But .5 Should Be Floored
Recode Multiple Columns Using Dplyr
How to Automate Nested Sections in Rmds Which Include Text, Maps and Tables
Color Bar Missing in Ggplot Legend, Windows Remote Desktop
R: Get Element by Name from a Nested List
Replace Na with Mode Based on Id Attribute
Count Number of Distinct Values in a Vector
Use 'J' to Select the Join Column of 'X' and All Its Non-Join Columns
How to Obtain All Combinations of the Columns of a Data Frame Taken by 2