ORDER BY within an INNER JOIN?

Question

This may have been answered but, I've read through all the 'similar titles' and have not found an answer that better describes what I'm trying to achieve.

Mysql query is:

SELECT
      inv.SKU,
      inv.misc1,
      inv.sizeletter,
      inv.has_children,
      ins_imb_1.ItemCustomerPriceLevel,
      ins_imb_1.ItemNumber,
      ins_imb_1.DiscountMarkupPriceRate1 
FROM inv
INNER JOIN 
      ins_imb_1 ON ins_imb_1.ItemNumber 
      LIKE CONCAT( inv.SKU,  '%' ) 
      AND ins_imb_1.ItemCustomerPriceLevel = 'M' 
WHERE inv.parent_sku = '' 
      AND inv.store_quantity > 0 
      AND inv.SKU LIKE  'ABC%' 
      AND inv.visible =  'Y' 
GROUP BY inv.SKU
ORDER BY inv.SKU ASC

and the results it returns are:

SKU     sizeword    sizeletter  has_children    ItemCustomerPriceLevel  ItemNumber  DiscountMarkupPriceRate1   
ABC107  NULL            L           Y                   M               ABC107L             15.95
ABC108  NULL            L           Y                   M               ABC108S             15.95
ABC109  NULL            L           Y                   M               ABC109XX            17.45

sizeletter is showing up as L(large) for all items yet ItemNumber has different sizes (represented by the last letter) and therefore sometimes a different price (as our larger sizes get a markup.)

What can I do to make sure my inner join goes in order of what's available? (which are alphabetical so typically L, M, S , X etc)

So ItemNumber will always end in L unless there is none in which case it tries M etc.

Thanks in advance!

Your SQL doesn't look like it's valid. Shouldn't there be something after "INNER JOIN"? And shouldn't the acs at the end be asc? — dcp, Feb 24 '12 at 16:01
Invalid SQL you probably missed something when copying it over — PinnyM, Feb 24 '12 at 16:02
oops I copied into notepad to format and must have removed a chunk on accident! — willboudle, Feb 24 '12 at 16:07
I don't really understand what you are trying to do here... if your sizes are significant to the query, why haven't you included them in the group by? — Poodlehat, Feb 24 '12 at 16:13
they are only significant in that I want the first available price when inner joining (which should usually be ABC123L), afterwards I use php to display the sizes in a dropdown menu. — willboudle, Feb 24 '12 at 16:20

score 0 · Answer 1 · edited May 23 '17 at 12:12

You can't sort within a join because it makes no sense to do so. You can however order your joined set of data by any column you like, even those used in joins.

From what I can understand of your question (which is hard because your query is half-missing as of this writing), what you want is a secondary sort which can be achieved like this

ORDER BY `inv`.`SKU` ASC, `ItemNumber` ASC

EDIT 1

It seems to me that you try to pack too many requirements into one single query. What you are trying to do is possible using sub-queries, but I strongly advise against them since they are dreadful performance-wise. In any case, here is how they work (your example in pseudocode):

SELECT 
   `SKU`, 
   (SELECT `ItemNumber` FROM `t2` WHERE `ItemNumber` LIKE CONCAT(t1.SKU, '%') ORDER BY `ItemNumber` ASC LIMIT 1) AS 'firstAvailable'
FROM `t1`
ORDER BY `SKU` ASC;

I suggest you leave the order be and let php do the more specific sorting post-SQL since you have to iterate over the entire thing anyway.

Also: I suggest normalizing the table, if possible. Assuming that ItemNumber uses part of SKU makes sense for a human, but it's incredibly resource intensive to make it clear to a DB. Putting the SKU into the ins_imb_1 as a foreign key is a pretty easy fix, makes your query faster and makes your data more stable against data corruption (it's a one-to-many relationship in your case).

I'm sorry I've since updated it and tried that, but am still getting ItemNumbers out of order (and therefore sometimes the markup price!) — willboudle, Feb 24 '12 at 16:21
Hmm thanks, I don't have the ability to normalize the table, not my db :( the thing is I only need the price once (the lowest price) and in the above example I get 'ABC109XX = 17.45' when 'ABC109L' and 'ABC109S' etc should be $15.95 — willboudle, Feb 24 '12 at 17:24
unless you're saying restructure my query to get the price for all children(sizes) and then use the lowest via php!! that is probably more efficient huh? — willboudle, Feb 24 '12 at 17:27
Yes, that would be more efficient. The thing is, you are asking SQL to do 2 things in one query which it simply cannot do. Either you get all results sorted by 2 values to get a list that is ready to be processed by PHP, you split the job up into 2 queries or you hack around the problem and get an ineffective and slow solution. — Mike, Feb 27 '12 at 07:40

score 0 · Answer 2 · answered Feb 24 '12 at 17:12

If I understand correctly your ins_imb_1.ItemNumber column has two pieces of information stored, both SKU and sizeletter. So, I think your JOIN condition:

ON ins_imb_1.ItemNumber 
      LIKE CONCAT( inv.SKU,  '%' )

should be instead:

ON ins_imb_1.ItemNumber = CONCAT( inv.SKU,  inv.sizeletter )

ORDER BY within an INNER JOIN?

2 Answers2

EDIT 1