ivanjanek ivanjanek - 4 months ago 9
SQL Question

Mysql sort BY number of filled columns

I'm comparing prices of products from

3
different website. So i have
3
different prices. If website doesn't offer that product, its price is empty.

MySQL looks like this:

**id | name | price_1 | price_2 | price 3**


I would like to sort it from products, which are available at all 3 websites. To products, that are available at just one website.

And I can't figure out how to approach this!

Answer

If the empty fields have NULL value, you can use

SELECT * FROM sometable ORDER BY ISNULL(price_1) + ISNULL(price_2) + ISNULL(price_3) DESC;

But the more sensible solution would be:

  • You have one table, which contains the products
  • You have another table, which contains the products ID, the price and a value which indicates which website provided that price.

Benefits:

  • You only have to COUNT(*) the entries of a product in the second table to find out on how many it is listed
  • You can change the number of websites any time without changing a table
Comments