How to get only first rows from table ordered by dublicating values in column?-CodePudding

I have a table looks like:

id | size | price | date
0  | 30   | 800   | 2021-10-01
1  | 30   | 900   | 2021-10-02
2  | 32   | 700   | 2021-09-11
3  | 30   | 800   | 2021-09-21
4  | 32   | 800   | 2021-09-01
5  | 32   |  0    | 2021-10-03

And i need to get the last updated prices of size <= 'size' to check it for zero and get first non-zero value. I try to sort table by size desc, date desc, but can't take only first rows with dublicating sizes.

SELECT *
    FROM (SELECT * 
            FROM `prices` 
                WHERE model_id = '269'
                    AND partner_id = '0'
                    AND size <= '32'
                  AND    date_time <= '2021-10-19' 
                 ORDER BY size DESC, date_time DESC
    ) AS t_1 
        GROUP BY size
        LIMI 1

does not help. The first result what i want is

id | size | price | date
5  | 32   |  0    | 2021-10-03
1  | 30   | 900   | 2021-10-02

then i want to get 900.

CodePudding user response：

Using exists logic we can try:

SELECT p1.*
FROM prices p1
WHERE NOT EXISTS (SELECT 1 FROM prices p2
                  WHERE p2.size = p1.size AND p2.date > p1.date);

More typically, on MySQL 8 , we would handle this using ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY size ORDER BY date DESC) rn
    FROM prices
)

SELECT id, size, price, date
FROM cte
WHERE rn = 1;

One advantage of using ROW_NUMBER is that should there be two or more latest records tied on the same date, we can simply add another sorting level to the ORDER BY clause to break the tie.

CodePudding user response：

SELECT * 
FROM prices p
WHERE 
    size <= '32' 
    AND date LIKE (SELECT max(date) from prices where size = p.size and price <> 0)