How to use SET with SELECT DISTINCT

Question

I have a table:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `pn` varchar(40) NOT NULL,
  `price` int(1) NOT NULL,
  `company` varchar(30) NOT NULL,
  `flag` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What I want to do is to find cheapest goods (pn) and I've managed to do it:

SELECT DISTINCT min(price), pn, company FROM `test` GROUP BY pn

But how I can also mark cheapest goods with flag. I want for all the results Update tabel, set flag=1. How to do that? Is it possible to use UPDATE table with SELECT DISTINCT?

Here is a SQL Fiddle: http://sqlfiddle.com/#!9/ea1b3f/9

That query is invalid. Won't execute on newer MySQL versions (unless in compatibility mode), may return unpredictable results with older MySQL versions. The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! — jarlh, Mar 26 '18 at 07:17
You should go for: `UPDATE SET flag = 'Y' WHERE ID IN (SELECT ID FROM ())`. — FDavidov, Mar 26 '18 at 07:19
Add some sample table data and the expected result, both for the SELECT and for the UPDATE. (Formatted text, not images.) — jarlh, Mar 26 '18 at 07:30

score 2 · Accepted Answer · answered Mar 26 '18 at 07:17

2

First, select distinct with group by is almost never the right thing to do. Presumably, you intend something like:

SELECT min(price), pn, min(company)
FROM `test`
GROUP BY pn;

If you want to set a flag for all the min prices, use a join:

update test t join
       (select pn, min(price) as minprice
        from test
        group by pn
       ) tt
       on t.pn = tt.pn and t.price = tt.minprice
    set t.flag = 1;

answered Mar 26 '18 at 07:17

Gordon Linoff

1,242,037
58
646
786

First SELECT doesn't work as expected. Always shows producer1 - please check on SQL fiddle. Possibly min(company) ? – Tikky Mar 26 '18 at 07:20
@tikky `company1` is the lowest company for each `pn`. – Barmar Mar 26 '18 at 07:26
1

@Tikky That query isn't supposed to return the company name for the lowest price. For that, see https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 – Barmar Mar 26 '18 at 07:27
@barmar "company1 is the lowest company for each pn" - not exacly. For pn "ram" cheapest company it is "company2", please see http://sqlfiddle.com/#!9/ea1b3f/9 And I need the company also (or id from chepaest source) – Tikky Mar 26 '18 at 08:57
@Gordon Linoff - can I use 'company' instead of min(company)? – Tikky Mar 26 '18 at 09:03
@Tikky min(company) is not the cheapest company, it's the lowest company name. Read the linked question to see how to get the cheapest company. – Barmar Mar 26 '18 at 14:13
@Tikky . . . This question is about prices. Nothing in the question is about "company". If you have another question, then ask it as a new *question*. – Gordon Linoff Mar 26 '18 at 20:14
@GordonLinoff thank you for the answer. Yes, I suppose to add information about producer in question also. Sorry for not clear question. – Tikky Mar 27 '18 at 08:19

How to use SET with SELECT DISTINCT

1 Answers1