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

Mysql – Return second highest date

I have a sales table, where it shows the information below

ID_sale sales_person sale_date
7 50 19/10/2022
6 43 17/9/2022
5 50 15/3/2022
4 43 13/2/2022
2 50 22/1/2022
3 10 05/2/2022
1 12 07/1/2022

and I want to create a query where I get the following information, basically the most recent date of the sale and the last sale date they made

ID_sale sales_person recent_sale last_sale
7 50 19/10/2022 15/3/2022
6 43 17/9/2022 13/2/2022
3 10 05/2/2022 05/2/2022
1 12 07/1/2022 07/1/2022

Thank you

Tags:

4

Answers


  1. 0

    We use rank() to find the most recent couple of sales and then pivot the results.

    select    max(recent_ID_sale)                         as ID_sale
         ,sales_person
         ,max(recent_sale)                            as recent_sale
         ,coalesce(max(last_sale), max(recent_sale))  as last_sale
from
(
select    sales_person
         ,case when rank() over(partition by sales_person order by sale_date desc) = 1 then sale_date end as recent_sale
         ,case when rank() over(partition by sales_person order by sale_date desc) = 2 then sale_date end as last_sale
         ,case when rank() over(partition by sales_person order by sale_date desc) = 1 then ID_sale   end as recent_ID_sale

from      t
) t
group by sales_person
order by recent_sale desc
    ID_sale sales_person recent_sale last_sale
    1 50 2022-10-19 2022-03-15
    2 43 2022-09-17 2022-02-13
    6 10 2022-02-05 2022-02-05
    7 12 2022-01-07 2022-01-07

    Fiddle

    Login or Signup to reply.
  2. 0

    You could add a CTE first where you add a row_nmber to get the order of the dates and then select them

    update

    I added a solution without GROUP BY and further window functions at the end

    CREATE TABLE t
    (ID_sale int, sales_person int, sale_date date)
;
    
INSERT INTO t
    (ID_sale, sales_person, sale_date)
VALUES
    (1, 50, '2022-10-19'),
    (2, 43, '2022-9-17'),
    (3, 50, '2022-3-15'),
    (4, 43, '2022-2-13'),
    (5, 50, '2022-1-22'),
    (6, 10, '2022-2-5'),
    (7, 12, '2022-1-7')
;


    Records: 7  Duplicates: 0  Warnings: 0

    WITH CTE AS 
 ( select 
  ID_sale, sales_person, sale_date,
  ROW_NUMBER() OVER(PARTITION BY sales_person ORDER BY sale_date DESC) rn
from   t)
 SELECT
  MIN(ID_sale), sales_person,
  (SELECT sale_date FROM CTE WHERE sales_person = c1.sales_person and  rn = 1) recent_sale ,
  COALESCE(
  (SELECT sale_date FROM CTE WHERE sales_person = c1.sales_person and  rn = 2),
  (SELECT sale_date FROM CTE WHERE sales_person = c1.sales_person and  rn = 1)) last_sale 
  FROM CTE c1
  GROUP BY sales_person
  ORDER BY recent_sale DESC;
    MIN(ID_sale) sales_person recent_sale last_sale
    1 50 2022-10-19 2022-03-15
    2 43 2022-09-17 2022-02-13
    6 10 2022-02-05 2022-02-05
    7 12 2022-01-07 2022-01-07 
    WITH CTE AS 
 ( select 
  ID_sale, sales_person, sale_date,
  ROW_NUMBER() OVER(PARTITION BY sales_person ORDER BY sale_date DESC) rn
from   t)
 SELECT
  ID_sale, sales_person,
  Sale_date  recent_sale ,
  COALESCE(
  (SELECT sale_date FROM CTE WHERE sales_person = c1.sales_person and  rn = 2),
  Sale_date) last_sale 
  FROM CTE c1
  WHERE rn = 1
  ORDER BY recent_sale DESC;
    ID_sale sales_person recent_sale last_sale
    1 50 2022-10-19 2022-03-15
    2 43 2022-09-17 2022-02-13
    6 10 2022-02-05 2022-02-05
    7 12 2022-01-07 2022-01-07

    fiddle

    Login or Signup to reply.
  3. 0

    Use ROW_NUMBER() to filter most recent rows and LEAD() for last_sale. Use COALESCE() for the case when no last_sale exists:

    with cte as (
  select
    ID_sale,
    sales_person,
    sale_date as recent_sale,
    lead(sale_date) over w as last_sale,
    row_number() over w as rn
  from sales
  window w as (partition by sales_person order by sale_date desc)
)
select 
  ID_sale,
  sales_person,
  recent_sale,
  coalesce(last_sale, recent_sale) as last_sale
from cte
where rn = 1
order by ID_sale;

    For older versions that don’t support window function (like ROW_NUMBER() and LEAD()) you can use correlated (by sales_person) subqueries for last_sale and for the recent row filter in the WHERE clause:

    select 
  s.ID_sale,
  s.sales_person,
  s.sale_date as recent_sale,
  coalesce((
    select
    max(sale_date)
    from sales s2
    where s2.sales_person = s.sales_person
      and s2.sale_date    < s.sale_date
  ), s.sale_date) as last_sale
from sales s
where sale_date = (
  select max(sale_date)
  from sales s1
  where s1.sales_person = s.sales_person
)
order by ID_sale;
    Login or Signup to reply.
  4. 0

    Not as efficient as a window function, but simpler and portable:

    select max(t1.ID_sale), t1.sales_person, t1.recent_sale, max(t2.recent_sale) as last_sale
from mytable t1
left join mytable t2 on t2.sales_person = t1.sales_person
and t2.ID_sale < t1.ID_sale
group by 2, 3

    Unless you have 100’s of millions of sales, this will perform OK if you have an index on sales_person.

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

Back To Top
Search