CODEWITHSUNDEEP
phpmysqlsqlgroup-bygreatest-n-per-group
Ole_S
Ole_S

Reputation: 456

mysql group and order rows

I have following table 'persons' with same persons in different rows

id |   firstname   |   surname   | date_created
------------------------------------------------------
3  | Nelli         | Schaller    | 2017-08-22 20:57:19
------------------------------------------------------
4  | Carl          | Schaller    | 2019-06-21 08:29:45
------------------------------------------------------
48 | Nelli         | Schaller    | 2020-06-25 13:06:09
------------------------------------------------------   
49 | Carl          | Schaller    | 2020-06-25 13:06:09

What I want to get are all unique Schallers with the biggest id / newest date_created value.

I tried this

SELECT id, CONCAT(surname, ", ", firstname) AS person, date_created
FROM persons
WHERE
surname LIKE "schall%"
GROUP by firstname, surname 
ORDER BY date_createdDESC, surname ASC LIMIT 0, 10

but get only as expected the first two entries (id 3 and 4) but I need 48 and 49. As mentioned in some comment in this case the LIKE statement isn't necessary but in real live it will be the source for an autocomplete field so I need the LIKE Any idea how to manage that?

Upvotes: 0

Views: 57

Answers (3)

pkr
pkr

Reputation: 1771

SELECT p.*
FROM persons p
LEFT JOIN persons p2 ON p2.firstname = p.firstname 
    AND p2.lastname = p.lastname
    AND p2.date_created > p.date_created
WHERE p2.id IS NULL

This is SQL Server syntax but MySQL is probably similar.

I'm assuming your id field doesn't need to be checked as well as the date_created since it's an identity column and would be larger anyway for the latter created records, but obviously adjust to your actual data.

Upvotes: 0

forpas
forpas

Reputation: 164204

Use NOT EXISTS:

SELECT p.id, CONCAT(p.surname, ', ', p.firstname) AS person, p.date_created
FROM persons p
WHERE p.surname LIKE '%schall%'
AND NOT EXISTS (SELECT 1 FROM persons WHERE firstname = p.firstname AND surname = p.surname AND id > p.id)
ORDER BY p.date_created DESC, person

If the condition to pick the latest of each group is the column date_created then change:

...AND id > p.id

with

...AND date_created > p.date_created

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

You could use subquery with group for max id

select t.max_id, t.person, m.date_created
from (
    SELECT max(id) max_id, CONCAT(surname, ", ", firstname) AS person
    FROM persons
    WHERE surname LIKE "schall%"
    ORDER BY date_createdDESC, surname ASC 
    GROUP BY CONCAT(surname, ", ", firstname)

    ) t 
    inner join  persons m ON  CONCAT(m.surname, ", ", m.firstname) = t.person
        and m-id = t.max_id

Upvotes: 0

Related Questions