Dimitry Dimitry - 7 months ago 11
SQL Question

Select unique rows, according to value in a secondary column

I have a simple mysql table:

id, code, product_name, supplier
1, 3844, ProductName1, Supplier1
2, 3844, ProductName1, Supplier2
3, 1233, ProductName2, Supplier2


I want to query all distinct products from table. So sql code is
select * from table group by code
.

My problem:

I want to set supplier priority in "group by" statement (e.g. Supplier1 is most relevant, but can be absent in list for a given
code
). Products #1 and #2 are the same. In my resulting table I want to see:

id, code, product_name, supplier
1, 3844, ProductName1, Supplier1
3, 1233, ProductName2, Supplier2


How can I accomplish this?

Answer

You probably need this:

SELECT
  MIN(id) id,
  code,
  MIN(product_name) product_name,
  MIN(supplier) supplier
FROM
  yourtable
WHERE
  (code, CASE WHEN supplier='Supplier1' THEN '' ELSE supplier END)
  IN (SELECT   code, min(CASE WHEN supplier='Supplier1' THEN '' ELSE supplier END)
      FROM     yourtable
      GROUP BY code)
GROUP BY
  code
ORDER BY
  min(id);

Please see fiddle here.

Comments