Question 20588 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL Pulling data from nearest date for multiple months – Mysql

I have the following data of date and color in a mysql database

2023-01-28 red
2023-01-29 blue
2023-01-30 blue
2023-01-31 red
2023-02-01 blue
2023-02-02 blue
2023-02-03 blue
2023-02-04 blue
2023-02-05 red

How can I pull the closest date to current date from each month and the associated color? Example answer for above code would be

2023-01-31 red
2023-02-05 red
Tags:

3

Answers


  1. 0

    I do not have MySQL, therefore, I am going to use SQL Fiddle.

    -- 1 - Create table
CREATE TABLE IF NOT EXISTS colors 
(
    the_date DATE,
    the_color VARCHAR(16)
)  ENGINE=INNODB;

-- 2 - Add data
INSERT INTO colors VALUES ('2023-01-28', 'red');
INSERT INTO colors VALUES ('2023-01-29', 'blue');
INSERT INTO colors VALUES ('2023-01-30', 'blue');
INSERT INTO colors VALUES ('2023-01-31', 'red');
INSERT INTO colors VALUES ('2023-02-01', 'blue');
INSERT INTO colors VALUES ('2023-02-02', 'blue');
INSERT INTO colors VALUES ('2023-02-03', 'blue');
INSERT INTO colors VALUES ('2023-02-04', 'blue');
INSERT INTO colors VALUES ('2023-02-05', 'red');

    Steps 1 & 2 create the table and add the data. Step 3 will want to covert the date into YYYYMM format for grouping and aggregation. Then group by that new fields and the color, selecting the date that is the largest using the MAX() function.

    I could have left the new column in the final results, but you did not have it in the result set. Therefore, use a derived table to get the result in the () inner statement and select the two fields (columns) you want in the outer statement.

    -- 3 - Solve the problem
SELECT
  the_color,
  the_largest_dte
FROM
(
SELECT 
  DATE_FORMAT(the_date, '%Y%m') as the_yyyy_mm,
  the_color,
  max(the_date) as the_largest_dte
FROM colors
GROUP BY
  DATE_FORMAT(the_date, '%Y%m'),
  the_color
) AS D

    The screen shot below shows everything together.

    enter image description here

    http://sqlfiddle.com/#!9/1700b5/7/0

    Login or Signup to reply.
  2. 0

    As i mentioned in the comment to the question, you have to use MAX function.

    I’m not sure i understand your requirements, but i think you want to get all instance of color which date is the newest…

    WITH MD AS
(
  SELECT MAX(datefld) as maxdate
  FROM mytable
),
CR AS
(
  SELECT datefld, colorfld
  FROM mytable AS MT
  INNER JOIN MD
    ON MT.datefld = MD.maxdate
)
SELECT MT.*
FROM CR
INNER JOIN mytable AS MT
  ON MT.colorfld = CR.colorfld
    AND MT.datefld <= CR.datefld
ORDER BY MT.datefld;

    SQLFiddle

    Result:

    datefld colorfld
    2023-01-28 00:00:00 red
    2023-01-31 00:00:00 red
    2023-02-05 00:00:00 red
    Login or Signup to reply.
  3. 0

    I got your desired output.

    with td as (select *,datediff(CURRENT_DATE(),tdate) ds from temp_data)
select tdate,color from td where ds in
  (
with tp as(
select month(tdate), min(datediff(CURRENT_DATE(),tdate)) as days from temp_data group by month(tdate))
select days from tp)

    https://dbfiddle.uk/92RoM59i

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search