Return second highest date

Question

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

This could help: https://stackoverflow.com/questions/5360894/get-the-second-highest-value-in-a-mysql-table — Swifty, Oct 23 '22 at 20:42
Is it really true that an earlier sale has a higher ID? Is ID_Sale an auto increment ID? If so , shouldn't it be greater for a later sale? — Bohemian, Oct 23 '22 at 21:46
oh no I apologies, the most recent sales will have greater id — Kishko, Oct 23 '22 at 21:58
I would assume that your date format is actually according to MySQL date datatype format and the date values you've posted in your question is from other source. — FanoFN, Oct 24 '22 at 00:35
@Kishko please edit your question to show realistic ID's for the rows (incrementing over time) and mention it's auto increment — Bohemian, Oct 24 '22 at 00:43

score 1 · Answer 1 · answered Oct 23 '22 at 20:54

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

nbk · Answer 2 · 2022-10-23T21:36:41.690

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

Why not just using `lead()`? Could be sooo much simpler and no GROUP BY required. — Paul Spiegel, Oct 23 '22 at 21:30

score 0 · Answer 3 · answered Oct 23 '22 at 21:55

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;

this needs two window functions and would be slower – nbk Oct 23 '22 at 23:05 — nbk, Oct 23 '22 at 23:05

score 0 · Answer 4 · answered Oct 23 '22 at 23:56

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.

Return second highest date

4 Answers4

update