MySQL Order By within Group By

Question

I know this has been extensively covered on Stackoverflow, but unfortunately I'm lost on the implementation for my specific bit of code.

Basically I have a listing page full of products, and certain products are listed twice, thrice or even more times. For a very good reason, but anyways.

I want to group by product name, so that the various products that have the same name don't show up more than once on the listing page. But I want the one with the lowest price to be the one selected to be displayed by the group by. Here is the code:

$listing_sql = "SELECT DISTINCT " . $select_column_list . "
p.products_id, p.products_type, p.master_categories_id,
p.manufacturers_id, p.products_price,  p.products_tax_class_id,  
pd.products_description, pd.products_name, IF(s.status = 1,
s.specials_new_products_price, NULL) as specials_new_products_price,  
IF(s.status =1, s.specials_new_products_price,
p.products_price) as final_price, p.products_sort_order,
p.product_is_call, p.product_is_always_free_shipping,
p.products_qty_box_status";

$listing_sql .= " FROM " . TABLE_PRODUCTS . " p" .

" LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id" .
" LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id =
pd.products_id" .
" JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id =
p2c.products_id" .
($filter_attr == true ? " JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " p2a on
p.products_id = p2a.products_id" .
" JOIN " . TABLE_PRODUCTS_OPTIONS . " po on p2a.options_id =
po.products_options_id" .
" JOIN " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov on
p2a.options_values_id = pov.products_options_values_id" .
(defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK') ? " JOIN " .
TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " p2as on p.products_id =
p2as.products_id " : "") : '');

$listing_sql .= " WHERE p.products_status = 1
and p.products_quantity > 0
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p2c.categories_id = '" . (int)$current_category_id . "'" .
$filter .
" group by pd.products_name " .
$having .
$alpha_sort;

The group by implemented near the end:

" group by pd.products_name " .

works fine. But obviously it groups by the first one it found, and so the one with the lowest price gets buried away if it was not the first version of the product entered into the database. The price column, as like in the query, is:

p.products_price

I've looked at and tried to implement some examples from here: Select the 3 most recent records where the values of one column are distinct

I just can't get them right - my understanding of MySQL queries is just really bad. Any help would be appreciated.

UPDATE:

SELECT  DISTINCT  p.products_id, p.products_type, p.master_categories_id,
p.manufacturers_id, p.products_price,  p.products_tax_class_id, 
pd.products_description, pd.products_name, IF(s.status = 1,
s.specials_new_products_price, NULL) as specials_new_products_price,     
IF(s.status =1, s.specials_new_products_price, p.products_price) as 
final_price, p.products_sort_order, p.product_is_call, 
p.product_is_always_free_shipping, p.products_qty_box_status

FROM zen_products p
LEFT JOIN zen_specials s on p.products_id =   
s.products_id
LEFT JOIN zen_products_description pd on p.products_id = pd.products_id
JOIN zen_products_to_categories p2c on p.products_id = p2c.products_id
WHERE p.products_status = 1
and p.products_quantity > 0
and pd.language_id = '1'
and p2c.categories_id = '3'

GROUP BY pd.products_name

you can try something similar to this: http://www.xpertdeveloper.com/2012/11/order-by-with-group-by-in-mysql/ — Avinash, May 28 '15 at 12:18
Yeah, the PHP code here isn't terribly useful because the problem is with the query, not the PHP. This is really a MySQL question. Posting the generated SQL and removing the PHP should help — Machavity, May 28 '15 at 12:19
You have a GROUP BY clause with no aggregating functions. Irritatingly MySQL allows and even optimizes this aberration. But it's potentially confusing and generally best avoided. It's also nonsensical in the context of a DISTINCT operator. — Strawberry, May 28 '15 at 12:26
Really appreciate the quick reply - sorry for taking so long to respond :( Please see my update. Is that what you meant by the generated SQL? — StuyvesantBlue, May 28 '15 at 17:55

score 0 · Answer 1 · answered May 28 '15 at 12:30

0

I'm not wading through all that, but the general solution is as follows:

SELECT x.* -- DISTINCT would be redundant in this context.
  FROM my_table x
  JOIN 
     ( SELECT grouping_column
            , MIN(ordering_column) n 
         FROM my_table 
        GROUP 
           BY grouping_column
     ) y
    ON y.grouping_column = x.grouping_column
   AND y.n = x.ordering_column;

answered May 28 '15 at 12:30

Strawberry

32,714
12
37
56

Thanks. I'm stuck on implementing this. I'm not sure how to take into account that the grouping column and ordering column are in two separate tables. So in your example, FROM my_table x (let's say that will be for the TABLE_PRODUCTS, which is where the price comes from) - but what about TABLE_PRODUCTS_DESCRIPTION, since that's where the product's name comes from. – StuyvesantBlue May 28 '15 at 17:57
How do you know which description in the descriptions table belongs to which product in the products table? – Strawberry May 28 '15 at 18:15
They are linked by the products ID. p.products_id and pd.products_id. Please see I updated my original post to show just the SQL query. – StuyvesantBlue May 28 '15 at 18:19
So there's your 'grouping column'!! X and Y are not two separate tables. They are two instances of the same table. Whatever else you decide to join to them is up to you. – Strawberry May 28 '15 at 18:20
Oh...sorry I'm confused. I want to group by the product's name, not by their ID's... – StuyvesantBlue May 28 '15 at 18:23
What's the difference? – Strawberry May 28 '15 at 18:31
ID's are unique, product name's aren't. That's why I need to group by product names. – StuyvesantBlue May 28 '15 at 22:57
In that case you have to include the join in both the main query and the subquery – Strawberry May 29 '15 at 00:10

MySQL Order By within Group By

1 Answers1