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

Question

I have a table of location which has 'Date column'. I have to find recent date by each group of locationID for e.g. locationID 1 has most recent date '31 May 2022'. After finding recent date from the group of locationID I have to add 14 days in that recent date and store it in NewDate column. and add + 1 in that new date for other row for that group of locationID.

My table is:

id  locationID    Date        NewDate   
1       1       31 May 2022
2       1       16 May 2022
3       1       28 Apr 2021
4       2       29 Mar 2022
5       2       22 Feb 2022
6       3       14 Jun 2022
7       3       27 Oct 2021
8       4       01 Feb 2022
9       4       04 May 2022
10      4       14 Jun 2021
11      5       01 Jun 2022
12      5       29 May 2022
13      5       20 Sep 2022
14      5       11 Aug 2022
15      5       03 Aug 2022

Answer should be as below:

For e.g. for locationID = 1

   id     locationID       Date             NewDate   
    1       1         31 May 2022       14 Jun 2022    // Recent Date + 14 Days - 31 May + 14 Days
    2       1         16 May 2022       15 Jun 2022    // Recent Date + 15 Days - 31 May + 15 Days
    3       1         28 Apr 2021       16 Jun 2022    // Recent Date + 16 Days - 31 May + 16 Days

I have come across few similar post and found recent date like this:

SELECT L.*
FROM Locations L 
INNER JOIN 
    (SELECT locationID, MAX(Date) AS MAXdate
     FROM Locations 
     GROUP BY locationID) groupedL
     ON L.locationID = groupedL.locationID
     AND L.Date = groupedL.MAXdate

using above code I am able to find recent date per location but how do I add and increment required days and store it to NewDate column ? I am new to MariaDB, please suggest similar post link, any reference documents or blogs. Should I make some function to perform this logic and call the function to store required dates in NewDate column? I am not sure please suggest. Thank you.

RESULT SHOULD LOOK LIKE BELOW:

id     locationID       Date             NewDate   
    1       1         31 May 2022       14 Jun 2022    // Recent Date for locationid 1 + 14 Days - 31 May + 14 Days
    2       1         16 May 2022       15 Jun 2022    // Recent Date for locationid 1 + 15 Days - 31 May + 15 Days
    3       1         28 Apr 2021       16 Jun 2022    // Recent Date for locationid 1 + 16 Days - 31 May + 16 Days
    4       2         29 Mar 2022       12 APR 2022    // Recent Date for locationid 2 + 14 Days
    5       2         22 Feb 2022       13 APR 2022    // Recent Date for locationid 2 + 15 Days
    6       3         14 Jun 2022       28 JUN 2022    // Recent Date for locationid 3 + 14 Days
    7       3         27 Oct 2021       29 JUN 2022    // Recent Date for locationid 3 + 15 Days
    8       4         01 Feb 2022       18 MAY 2022    // Recent Date for locationid 4 + 14 Days
    9       4         04 May 2022       19 MAY 2022    // Recent Date for locationid 4 + 15 Days
    10      4         14 Jun 2021       20 MAY 2022    // Recent Date for locationid 4 + 16 Days
    11      5         01 Jun 2022       04 OCT 2022    // Recent Date for locationid 5 + 14 Days
    12      5         29 May 2022       05 OCT 2022    // Recent Date for locationid 5 + 15 Days
    13      5         20 Sep 2022       06 OCT 2022    // Recent Date for locationid 5 + 16 Days
    14      5         11 Aug 2022       07 OCT 2022    // Recent Date for locationid 5 + 17 Days
    15      5         03 Aug 2022       08 OCT 2022    // Recent Date for locationid 5 + 18 Days

@Stu I am using phpmyadmin. But I always try queries on dbfiddle - MySQL 8.0 — Rob04, Mar 09 '22 at 22:13
@Stu phpmyadmin - Server version: 10.2.30-MariaDB - MariaDB Server — Rob04, Mar 09 '22 at 22:17
You reply both MySQL 8.0 and MariaDB 10.2? Which is it? Phpmyadmin is a client that can connect to either, but the client is irrelevant. — Bill Karwin, Mar 09 '22 at 22:33
@Stu Thank you for your help. I finally figure it out. Without your help it won't be possible. Much appreciated and my apologies for all the inconveniences. In my table I added new column - name of location and instead of Date column I ordered by Name. Now I am getting values in order how I wanted it. Thank a lot. — Rob04, Mar 10 '22 at 22:11
@Rob04 remember I can't see your data and what you are doing, I only have the sample data in the DB fiddle to work with :) — Stu, Mar 10 '22 at 22:13

score 2 · Answer 1 · answered Mar 09 '22 at 22:24

2

You can use a cte:

with cte as (
   select l1.*, l2.m, (select sum(l4.id < l1.id and l4.locationid = l1.locationid) from locations l4) inc from locations l1 
   join (select l3.locationid, max(l3.dt) m from locations l3 group by l3.locationid) l2 on l1.locationid = l2.locationid
)
select c.id, c.locationid, c.dt, c.m + interval 14 + c.inc day from cte c

answered Mar 09 '22 at 22:24

Ajax1234

69,937
8
61
102

Can you please add little explanation as well. Really appreciate it. – Rob04 Mar 09 '22 at 22:29
@Rob04 The `cte` finds the maximum date for each location, and also finds the offset increment for each date in a location range by using a subquery. Then, the final `select` statement simply adds the 14 day interval, plus the increment, to the most recent date for each location range. – Ajax1234 Mar 09 '22 at 22:40
cte is not working in MariaDB 10.2. Am I missing something here. Can you please share working example with the sample data I posted in Question ? – Rob04 Mar 09 '22 at 22:42

Stu · Accepted Answer · 2022-03-10T20:43:40.057

0

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

edited Mar 10 '22 at 20:43

answered Mar 09 '22 at 22:53

Stu

30,392
6
14
33

Thank you so much for response. In DB Fiddle is working fine but when I applied on actual table its giving me correct calculation but not in order. The First Recent date + 14 days should display in 1st row of each location group than 2nd and so on. But I am getting weird order – Rob04 Mar 09 '22 at 23:09
A resultset requires an order-by clause, I have added in the answer above, hopefully that resolves for you. – Stu Mar 09 '22 at 23:13
1

Thank you so much for your help. I learned something new today. Issue resolved. – Rob04 Mar 09 '22 at 23:22
Is it possible to store result of this query in already existing column in same table ? When I run the query its giving me an output but how do I store the results in NewDate column which is already in Table ? Do I have to create function or Trigger ? Please advice. – Rob04 Mar 10 '22 at 15:55
`WITH CTE AS ( select Id, locationID, Date, 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 ) UPDATE t SET t.NewDate = CTE.NewDate FROM CTE WHERE CTE.id = t.id and CTE.locationID = t.locationID` I Tried above code using example Table but its not working. @Stu – Rob04 Mar 10 '22 at 17:56
@Rob04 - that's a different issue to the one you asked and I answered above - don't move the goalposts, ask a new question. – Stu Mar 10 '22 at 18:58
Sir How it is different issue..as if you please read again my question I already mentioned that I want to store result in NewDate column and how my table looks like + How should result look like. – Rob04 Mar 10 '22 at 19:13
@Rob04 Ok my bad I did miss the part about updating the column. Please see [this modified Fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1fb3dc69e3cc3c7802443435dc02825a) - does that now full help you? – Stu Mar 10 '22 at 19:24
on phpmyadmin I got error - "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'update locations join nd on locations.id = nd.id set locations' at line 8 – Rob04 Mar 10 '22 at 19:38
I dont know why its giving me error at "update table_name" line – Rob04 Mar 10 '22 at 19:52
I don't use MariaDB but it seems to not like joining with a CTE. Changing to a subquery [seems fine](https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=3e197da52ff6792d68c3694317208f9a) though? – Stu Mar 10 '22 at 19:55
yes this one is working fine but I am again getting result in wrong order in NewDate column. I added "order by 'locationID', 'NewDate' " still its same result. – Rob04 Mar 10 '22 at 20:11
@Rob04 I'm not sure what you mean by wrong order - the update will be assigning the `newdate` based on the `id`, as you can see in the fiddle `05-31` is assigned `06-14`, `05-16` is assigned `06-15` etc. There's no "ordering" involved in this solution, it's a 1-to-1 update. Can you amend the fiddle to show what you mean - ordering applies to how you query the data, not how you update it. – Stu Mar 10 '22 at 20:16
okay so my bad i didn't check each location wise becasue I have very big table. So in your solution for each location it takes recent date + add 14 days and stored in same row. For e.g. in Maria db fiddle - please check result for locationid = 4. For locationid 4 2nd date "2022-05-04" is recent date, so result should be `05-18` , `05-19` , `05-20` instead of `05-19` , `05-18`, `05-20` . Same for locationid 5. order is wrong, it should be 4,5,6,7,8 oct in order. – Rob04 Mar 10 '22 at 20:30
even before we apply update query , your first solution is giving correct results for location 4 and 5 – Rob04 Mar 10 '22 at 20:32
@Rob04 You're getting hung up on the order you see on screen. Check the *data* - in the output from the select note the DATE column for LocationID 4 is shown on-screen because of the *order by criteria*. Check the DATE vs NEWDATE value. In the fiddle I was (my mistake) ordering the results by ID. Just *[order by the same criteria](https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=af1e991f3c0106610554969f70fc1f59)* but that's not the problem you are solving, you want the correct calculated NEWDATE value assigned with the existing DATE value - it's doing that! – Stu Mar 10 '22 at 20:38
I understood your point but I need to store the value in order. When I apply update query on table and check with Select statement with order by LocationId, NewDate; I am not getting correct result stored in table. it should be same as you said " 05-31 is assigned 06-14, 05-16 is assigned 06-15 etc." but its not. Its giving me 1st 14 days calculation in the same row where the recent date is stored. For e.g. If recent date is found for group of location 4 in 3rd row.. than it saves that date + 14 day in 3 rd row instead of 1st. – Rob04 Mar 10 '22 at 20:49
Tables don't have any inherent ordering, you really have lost me. I've [changed the fiddle](https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=21551659115e560e61166e1bf10e59bf) to update the table joining on the LocationId and Date - naturally it's the same outcome - and the results from the base table are identical to the output of the query on its own (which you already stated was correct). – Stu Mar 10 '22 at 21:01
is it possible that this update query runs automatically everytime when there is an update in Date column ? Dale column is autogenerated using function..and stored in database using Trigger. Currently whenever there is an update in Date column( which is updated when Trigger fire) I have to manually run this update query to store NewDate. I know it is different Question, just wanted your advice if its possible. – Rob04 Mar 11 '22 at 17:14
I personally don't use MariaDB so am unable to advise, although I'm sure it would be possible. – Stu Mar 11 '22 at 18:04

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

2 Answers2