I need to exclude partial duplicate from my query (MariaDB 5.5.68), for example :
SELECT num, returned FROM table WHERE returned = 1;
| num(VARCHAR) | returned |
|---|---|
| 29.0 | 1 |
| 27.0 | 1 |
| 22.0 | 1 |
| 22.1 | 1 |
| 21.0 | 1 |
In this example I need to exclude 22.0 and only keep 22.1
I have tried to convert the num row as DOUBLE and call MAX() but I think I need to do it as a subquery and I cannot get it working.
>Solution :
You can use aggregation to achieve this:
SELECT max(cast(num as decimal(4,2))), returned
FROM table
WHERE returned = 1
GROUP BY cast(num as int), returned
In the SELECT clause we cast to decimal for so that max() will return the numeric max and not the lexicographical max. In the GROUP BY clause we cast to int to round the number to integer for proper grouping.