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

how to retrieve data for the last 2 years with each data being different from the last year it was published – Mysql

I have the following data which has two columns journal & published_year

CREATE TABLE journals (
  journal VARCHAR(255) NOT NULL,
  published_year INT NOT NULL
);

INSERT INTO journals (journal, published_year) VALUES
('Journal A', 2022),
('Journal B', 2017),
('Journal A', 2021),
('Journal C', 2018),
('Journal A', 2020),
('Journal C', 2017),
('Journal A', 2019),
('Journal B', 2016),
('Journal D', 2021);

I want the result data like the following

+-----------+----------------+
| journal   | Last 2 Year    |
+-----------+----------------+
| Journal A | 2022,2021,2020 |
| Journal B | 2017,2016      |
| Journal C | 2018,2017      |
| Journal D | 2021           |
+-----------+----------------+
Tags:

3

Answers


  1. 0

    Use the GROUP_CONCAT function to concatenate the last 2 years for each journal anda separated by commas.

    SELECT journal, GROUP_CONCAT(published_year ORDER BY published_year DESC SEPARATOR ',') AS `Last 2 Year`
FROM journals
GROUP BY journal
ORDER BY journal;

    db<>fiddle output

    Login or Signup to reply.
  2. 0

    Group concatenate by year and set a condition for two years group by journal and it’s done.
    You can use exact year if you want to. I just used current year.
    Try this:

    SELECT journal, GROUP_CONCAT(published_year ORDER BY published_year DESC SEPARATOR ',') AS `Last 2 Year`
FROM journals WHERE published_year >= YEAR(GETDATE()) - 2
GROUP BY journal
ORDER BY journal;
    Login or Signup to reply.
  3. 0

    Using the window function row_number() to order data by year and per journal, then we get only the first 3 inputs :

    This is a working solution if there is no gaps between years.

    with cte as (
  SELECT row_number() over (partition by journal order by published_year desc) as row_id, t.*
  FROM journals t
)
SELECT journal, GROUP_CONCAT(published_year ORDER BY published_year DESC) AS `Last 2 Year`
FROM cte
where row_id <= 3
GROUP BY journal
ORDER BY journal

    Demo here

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

Back To Top
Search