select produktov a cien + usporiadanie

SELECT p.product_id, p.name, MIN(x.value)
FROM products p
LEFT JOIN prices x ON p.id = x.product_id
WHERE x.available IS NOT NULL
GROUP BY p.product_id, p.name
ORDER BY MIN(x.value)

SELECT p.product_id, p.name, MIN(x.value)
FROM products p
LEFT JOIN (SELECT product_id, value, value_discount FROM prices WHERE available IS NOT NULL) x ON p.id = x.product_id
GROUP BY p.product_id, p.name
ORDER BY MIN(x.value)

SELECT a.`product_id`, pr.`value`, pr.`value_discount`
FROM (`products` a)
LEFT JOIN (
   SELECT `product_id`, `value`, `value_discount`
   FROM (`prices`)
   ORDER BY (CASE WHEN `value_discount` > 0 AND `value_discount` < `value` THEN `value_discount` ELSE `value` END) asc
   LIMIT 1
) pr ON `a`.`product_id`=`pr`.`product_id`
ORDER BY (CASE WHEN pr.`value_discount` > 0 AND pr.`value_discount` < pr.`value` THEN pr.`value_discount` ELSE pr.`value` END) asc

SELECT `product_id`, `value`, `value_discount`
FROM (`prices`)
WHERE `product_id`=25561
ORDER BY (CASE WHEN `value_discount` > 0 AND `value_discount` < `value` THEN `value_discount` ELSE `value` END) asc
LIMIT 1

SELECT p.*, MIN(IF(pr.value_discount = 0, pr.value, LEAST(pr.value, pr.value_discount))) AS min_value
FROM products AS p
LEFT JOIN prices AS pr USING(product_id)
GROUP BY p.product_id
ORDER BY min_value

SELECT `product_id`, `value`, `value_discount`
FROM (`prices`)
WHERE `product_id`=25561
ORDER BY (CASE WHEN `value_discount` > 0 AND `value_discount` < `value` THEN `value_discount` ELSE `value` END) asc
LIMIT 1

ORDER BY (CASE WHEN `value_discount` > 0 AND `value_discount` < `value` THEN `value_discount` ELSE `value` END) asc

SELECT p.name, pr1.*
FROM prices AS pr1
INNER JOIN (
   SELECT product_id, MIN(IF(value_discount = 0, value, LEAST(value, value_discount))) AS min_value
   FROM prices
   GROUP BY product_id
) AS pr2 ON pr1.product_id = pr2.product_id AND (pr1.value = pr2.min_value OR pr1.value_discount = pr2.min_value)
RIGHT JOIN products AS p ON p.product_id = pr1.product_id
GROUP BY pr1.product_id
ORDER BY min_value

price_id            1       2       3       4       5       6       7
product_id          1       1       1       2       2       2       3
value               10      20      15      30      15      20      10
value_discount      0       10      10      20      0       15      5
available           1       1       0       1       1       0       0

$this->db->select('a.product_id, a.name');
$this->db->from('prices p');           
$this->db->join('products a', 'p.product_id=a.product_id', 'left');       

$this->db->group_by('p.product_id');

if ($order=='date')
{
    $this->db->order_by('UNIX_TIMESTAMP(a.date) asc');
    $this->db->order_by('a.product_id asc');
}
elseif ($order=='name')
{
    $this->db->order_by('a.name asc');
}
else
{
    $this->db->order_by("(CASE WHEN a.sorter is null THEN 1 ELSE 0 END) asc");
    $this->db->order_by('a.sorter asc');
    $this->db->order_by('a.product_id asc');
}

$products = $this->db->get()->result_object();

foreach ($products as $product)
{
    $this->db->select('pr.value, pr.value_discount');
    $this->db->from('prices pr');
    $this->db->where('pr.product_id', $product->product_id);

    $this->db->order_by("(CASE WHEN pr.available is null THEN 1 ELSE 0 END) asc");               
    $this->db->order_by("(CASE WHEN pr.`value_discount` > 0 AND pr.`value_discount` < pr.`value` THEN pr.`value_discount` ELSE pr.`value` END) asc");               
    $this->db->order_by("(CASE WHEN pr.`value_discount` > 0 AND pr.`value_discount` < pr.`value` THEN 0 ELSE 1 END) asc");

    $this->db->limit(1);

    $product->price = $this->db->get()->row_object();                       
}

$this->db->order_by("(CASE WHEN pr.available is null THEN 1 ELSE 0 END) asc");               
$this->db->order_by("(CASE WHEN pr.`value_discount` > 0 AND pr.`value_discount` < pr.`value` THEN pr.`value_discount` ELSE pr.`value` END) asc");               
$this->db->order_by("(CASE WHEN pr.`value_discount` > 0 AND pr.`value_discount` < pr.`value` THEN 0 ELSE 1 END) asc");

$this->db->limit(1);

$this->db->order_by("(CASE WHEN pr.availability = 'in-stock' THEN 0 ELSE 1 END) asc");

SELECT p.name, pr.*
FROM products AS p
INNER JOIN
(
   SELECT * FROM
   (
      SELECT *
      FROM prices
      ORDER BY available DESC, LEAST(value, IF(value_discount = 0, ~0, value_discount)), value_discount DESC
   ) AS tmp
    GROUP BY product_id
) AS pr USING(product_id)