Select Row With Most Recent Date Per User

Select row with most recent date per user

Query:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)

Result:

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

Note that if a user has multiple records with the same "maximum" time, the query above will return more than one record. If you only want 1 record per user, use the query below:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user            
                 ORDER BY t2.id DESC
                 LIMIT 1)

Select row with most recent date per location and increment recent date by 1 for each row by location using MariaDB

You could use analytic window functions and update the original table by joining to a sub-query (works for MariaDB):

update t
join (
  select Id, 
    Date_Add(First_Value(date) over(partition by locationId order by date desc), 
      interval (13 + row_number() over(partition by locationId order by date desc)) day 
  ) NewDate
  from t
)nd on t.id = nd.id
set t.Newdate = nd.NewDate;

See DB<>Fiddle example

Select row with most recent date per user where date is more than current date

Thanks to @TimBiegeleisen for the answer..
I've tried this code and it works..

This code for showing the 'update' rows:

select a.* from tmp a 
inner join
(select user, max(date) as date from tmp group by user)
b on a.user=b.user and a.date=b.date
where year(a.date +interval 1 year) >= year(current_date())
order by a.user

and this for showing the 'expired' rows:

select a.* from tmp a
inner join
(select user, max(date) as date from tmp group by user)
b on a.user=b.user and a.date=b.date
where year(a.date +interval 1 year) < year(current_date())
order by a.user

I hope my question and all the answers can help other's problems..

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

Get most recent date for each id

select ID, Max(Date) as Date 
from yourtable
group by ID

SQL Group By most recent date and sales value

SELECT ID, Name, Order, Date FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date DESC) AS sn
    FROM your_table_name
) A WHERE sn = 1;

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 all rows with 2 most recent dates by ID

You can try groupby().nth:

df[df['date']>=df.groupby("id")["date"].transform('nth', n=2)]

Output:

   id        date  value1  value2
0   a  2020-12-07      10    1000
1   a  2020-12-07      10    1000
2   a  2020-12-05      10    1000
3   a  2020-12-05      10    1000
6   b  2021-12-07      20    2000
7   b  2021-12-07      20    2000
8   b  2021-09-05      20    2000
9   b  2021-09-05      20    2000
12  c  2021-09-05      30    3000
13  c  2021-09-05      30    3000
14  c  2021-02-05      30    3000
15  c  2021-02-05      30    3000

Find most recent row per user and day in sql query

This query:

select Customer_id, Date::date, max(Date) maxdate from tablename group by customer_id, Date::date

gets the most recent date for each customer.

Join it to the main table to get the rows that contain the most recent date for each customer:

select t.Customer_id, t.date, tt.Payment from (
select Customer_id, Date::date date, max(Date) maxdate from tablename group by customer_id, Date::date
) t inner join tablename tt
on tt.customer_id = t.customer_id and tt.date = t.maxdate

---

Related Topics