MySQL Select duplicates with LEAST condition

Question

I'm trying to find duplicates and select the result with the least value combination in a table.

Until now I'm only able to select the result that has the lowest value on a column using MIN(). I thought it would be easy to just replace MIN with LEAST and change the columns.

Here's a layout:

 CREATE TABLE `index`.`products` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(10) NOT NULL , `price` INT NOT NULL , `availability` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

INSERT INTO `products` (`id`, `name`, `price`, `availability`) VALUES 
(NULL, 'teste', '10', '1'),
(NULL, 'teste', '5', '2'),
(NULL, 'teste', '3', '3');

The simplified layout

id - name - price - availabilty
1  - test - 10 - 1
2  - test - 5 - 2  
3  - test - 3 - 3

using the following query:

select name, MIN(price) from products group by name having count(*) > 1

gets me the lowest price. I'm trying to get the lowest price and lowest availabilty.

select name, LEAST(price, availability) from products group by name having count(*) > 1

This doesn't work.

Clarification: I want to select the row with the lowest price and lowest availabity. In this case it should be the first one I guess.

I should clarifity that 1=available, 2=not available and 3=coming soon

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 07 '17 at 19:19
But has the lowest availabity. I should clarifity that 1=available, 2=not available and 3=coming soon — xnny, Feb 07 '17 at 19:27
@Strawberry i added what you request I think. Sorry, I'm new at this kind of stuff — xnny, Feb 07 '17 at 20:42
I can't see the desired result - and I suspect that the data set isn't large enough to be properly representative of the problem. — Strawberry, Feb 07 '17 at 20:58
The desired result in my example is row 1. Lowest price for the lowest availability. I won't have more than 4 duplicated rows per product as I only have 4 suppliers. — xnny, Feb 07 '17 at 21:08

woocash · Accepted Answer · 2017-02-07T21:39:04.870

0

The statement to select lowest price for the best availability is:

set sql_mode=only_full_group_by;
SELECT 
    name, MIN(price), availability
FROM
    products
JOIN
    (
        SELECT 
            name, MIN(availability) availability
        FROM
            products
        GROUP BY name
    ) as x
USING (name , availability)
GROUP BY name , availability;

edited Feb 07 '17 at 21:39

answered Feb 07 '17 at 19:54

woocash

36
4

The duplicate is the name column. All 3 rows have the same name. – xnny Feb 07 '17 at 20:15
I'm getting the same error as a previous answer: #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'index.products.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – xnny Feb 07 '17 at 20:33
Ok! Almost there. This works if I remove the id from the 3rd SELECT. Otherwise it gives me the same error. I think I can work with this now. But do you think it's possible to have the id there? – xnny Feb 07 '17 at 20:53
I didn't testing it before with `only_full_group_by` now should be ok – woocash Feb 07 '17 at 20:57
Thank you @woocash. Unfortunately I don't have the id. However this works just fine! Thank you – xnny Feb 07 '17 at 21:03

MySQL Select duplicates with LEAST condition

1 Answers1