Limit query result per unique columns combination

Question

I would like to modify this query to limit result to have max 2 rows (latest) per group:

select
    distinct clusterName,
    aksNamespace,
    acrName,
    acrImageName,
    acrImageVersion,
    date
from
    (
    select
        clusterName,
        aksNamespace,
        acrName,
        acrImageName,
        acrImageVersion,
        date
    from
        aks_images
    order by
        acrImageName,
        date desc
) as t
where
    acrName = "storage"
order by
    clusterName,
    acrImageName,
    date desc

Current result:

clusterName	aksNamespace	acrName	acrImageName	acrImageVersion	`date`
dev	support	storage	app	f74581b	17.02.2023 14:35
dev	support	storage	app	c6040a0	17.02.2023 7:45
dev	support	storage	app	4410f39	16.02.2023 10:43
dev	abc	storage	qwer	93241f1	15.02.2023 12:45
dev	abc	storage	qwer	249b089	14.02.2023 13:15
dev	abc	storage	qwer	1c40785	13.02.2023 13:30
prod	support	storage	app	469a492	07.02.2023 14:15
test	support	storage	app	07e22a6	17.02.2023 14:40
test	support	storage	app	daf975d	17.02.2023 13:40
test	support	storage	app	7e1a50b	15.02.2023 13:10
test	support	storage	app	8f27715	15.02.2023 9:35

Expected result:

clusterName	aksNamespace	acrName	acrImageName	acrImageVersion	`date`
dev	support	storage	app	f74581b	17.02.2023 14:35
dev	support	storage	app	c6040a0	17.02.2023 7:45
dev	abc	storage	qwer	93241f1	15.02.2023 12:45
dev	abc	storage	qwer	249b089	14.02.2023 13:15
prod	support	storage	app	469a492	07.02.2023 14:15
test	support	storage	app	07e22a6	17.02.2023 14:40
test	support	storage	app	daf975d	17.02.2023 13:40

Mysql version: 8.0.31

I'd be grateful for any advice or solutions.

Answer 1

On MySQL 8+, we can use ROW_NUMBER():

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY clusterName, acrImageName
                                 ORDER BY date DESC) rn
    FROM aks_images
    WHERE acrName = 'storage'
)

SELECT
    clusterName,
    aksNamespace,
    acrName,
    acrImageName,
    acrImageVersion,
    date
FROM cte
WHERE rn <= 2
ORDER BY
    clusterName,
    acrImageName,
    date DESC;