Select max date of customer and retain the number of rows



  • I would like to know the max(date) of group by customer and still retain the number rows from the base table.

    customer_id date
    1 01-01-2022
    1 10-01-2022
    1 15-02-2022

    The result that I’m looking for

    customer_id date max_date
    1 01-01-2022 15-02-2022
    1 10-01-2022 15-02-2022
    1 15-02-2022 15-02-2022

    Query tried

    Select * 
    From table_1
    Group by customer_id;
    


  • See if you like this; it comes close to answering your question literally:

    SELECT customer_id,
           COUNT(*) AS num_rows,
           MAX(date) AS latest
        FROM tbl
        GROUP BY customer_id;
    

    But, if you want to show the other columns for all the rows for each customer, it gets messier.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2