What is with MySQL?

Manat

Hello all

This is my Where Clause:

WHERE
".$whereClause." 
ORDER BY 
prod.Nom ASC, prod.Price_Range_ID DESC 
". $limit;

Tell me, is there something wrong with it? I don't get any errors, no PHP errors whatsoever, the page parses fine, but.... the second clause after the comma doesn't happen.

It only orders by prod.Nom ASC, but not prod.Price_Range DESC.
Anyone know why?
Is this a limitation of MySQL since it's not relational? Ya that question makes no sense, but anyhow, anyone know why it doesn't work?

Thanks

nemonoman

You should be able to get a multi-column sort in MySQL with no trouble.

Maybe your results are being sorted as you requested, even though the results are not what you expect.

Your query says:

Sort the nom column first
THEN subsort the price_ID column.

For example, with records like this:

Nom C1 price 25
nom F price 10
nom B price 100
nom A price 20
nom D price 30
nom E price 50
nom C2 price 1000

Your return would be:
nom A price 20
nom B price 100
Nom C1 price 25
nom C2 price 1000
nom D price 30
nom E price 50
nom F price 10

Because there are no records with the same nom, there would be no subsort by price.

In fact the only time there would be a descending price subsort is where you had the same data in the nom field in 2 records.

if you had 2 'A' records for example:

nom A price 10
nom A price 20

Then your sorted records would return:

nom A price 20
nom A price 10
nom B price 100
Nom C1 price 25
nom C2 price 1000
nom D price 30
nom E price 50
nom F price 10

Only if you have >1 records with same nom data will your subsort on price kick in.

if you sorted by price, THEN subsorted by nom, your return would be:

nom C2 price 1000
nom B price 100
nom E price 50
nom D price 30
nom C1 price 25
nom A price 20
nom A price 10
nom F price 10

Perhaps you could post the actual undesired results of this query, showing columns as they are returned, and a descripton of the results you expected to see, so we can better understand how to interpret the problem.

Manat

Well...

nom C2 price 1000
nom B price 100
nom E price 50
nom D price 30
nom C1 price 25
nom A price 20
nom A price 10
nom F price 10

Would not be an option, because I would still want everything to be displayed in alphabetical order. I want everything to be displayed in alphabetical order, but subsorted by price.

Do you mean to say that when I do an order By, the subsort must lie within the parent sort? Meaning that I my above PHP/SQL code wouldn't work because there are only unique Nom fields? Can I not seperate the two, maybe through a nested order by or something? There must be a way....

Because the code is for a catalog system, and it sorts out all products by alphabetical order, but radomly within that order, but for product 'A', I would want the most expensive product 'A' to be shown first. But there are only unique Nom fields.

This is how it works:

product 'A hello'
Product 'A bye'

So the first letter is describes the order, but the words after it is always different. So A would be a category, and hello would be which product within that category.

i.e.

Microsoft Powerpoint
Microsoft Word
Microsoft Excel
Microsoft Exchange
ect...

The Nom is Microsoft Powerpoint, so If I order it by ASC, then Excel would show first, but I want the most expensive to show first, which would be Microsoft Exchange.

I'm sure a nested Order By would solve this, I just don't know how to implement it.

Thanks
MK

tomhath

If these are the values for Nom:

Microsoft Powerpoint
Microsoft Word
Microsoft Excel
Microsoft Exchange

Then that's the column it iwll sort on.

It sounds like the Nom field contains two pieces fo information (Group and Product). In that case you should split that column into the two component parts.

nemonoman

Tomhath has given you the RIGHT THING TO DO: Move your data into different columns.

It would then be a Piece Of Damned Cake to:

ORDER BY group ASC, price DESC

If you mean for this to be a useful, long-term project, you might want to do not only this, but to normalize your data into different tables as well.

THERE ARE OCCASIONS, however, for example when one is doing a Quick and Dirty select for a one-off report, and on those occasions, when one uses tricks instead of rearranging all one's data.

You could, for example, say

ORDER BY SUBSTRING(nom, 0 , 10) ASC, price DESC

that would sort all your MICROSOFTs (first 10 chars of nom col) subsorted by Price

If you have different 'groups' (ie Microsoft, Corel, Symantec, etc.) set the SUBSTRING length to the shortest length that still gives you the desired results.

This is CheesyCode and should only be used in emergencies. Better, much much better, to set up your data in a normal fashion.