Find the youngest customer grouped by province



  • SELECT a.province, c.birth_date, c.name 
    FROM customer c
    JOIN address a ON (c.cust_id = a.cust_id) 
    GROUP BY a.province 
    ORDER BY birth_date DESC;
    

    I want to find the youngest customer in each province. The query above doesn't work.



  • with CustomerRank as (
        select
            a.province
            ,c.name
            ,dense_rank() over(partition by a.province order by c.birth_date desc) as AgeRank
        from customer c
        join address a on c.cust_id = a.cust_id
    )
    select *
    from CustomerRank a
    where a.AgeRank = 1;
    

    Example fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=016888fbaf40093edfdf7ad22412c215

    The idea is to create a list on which you can then pick the answer you need. In this case, generating a ranking of birth_date per province (AgeRank) then picking all the rows where AgeRank is 1 (i.e. the youngest as we've order by birth_date desc).




Suggested Topics

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