GROUP BY with MAX on date

Question

I have table with id, visit date and phone number.


        id    phone_number   visit_date
       2131   +7935345432    2020-03-17
       2135   +7935345432    2020-03-17
       3021   +4953433245    2020-02-21
       3078   +4953433245    2020-01-26

I need to receive id of row for last visit by each phone group. For example:


        id    phone_number   visit_date
       2131   +7935345432    2020-03-17
       3021   +4953433245    2020-02-21

I have a query:

    SELECT id, phone_number, MAX(visit_date) 
    FROM test
    GROUP BY phone_number;

But there is an error that id is nonaggregated column. Using a subquery goes that query gives multiple duplicated dates if they have the same value. Help me please

Answer 1

SELECT
    table1.*
FROM
    (SELECT 
        phone_number,
        MAX(visit_date) as lastDate
    FROM 
        test1
    GROUP BY phone_number) as lasts,
    (SELECT * FROM test1) as table1
WHERE
    lasts.lastDate = table1.visit_date
    AND
    lasts.phone_number = table1.phone_number;

Выводит id-шники 1, 2, 3

БЛОГ НА HUSL

GROUP BY with MAX on date

Ответы (1 шт):