DISTINCT and ORDER BY

[recordscout]

Hi,

I have a table with product names and separate prices.
I want to show the distinct product names in alphabetical order, but at the same time show the cheapest price first.

I can of course use GROUP BY for the product names, but when I apply the order by PRODUCTNAME to the GROUP BY it will list the first product in the database and not the cheapest product. I can not use INNER JOIN as it is from one table.

I have tried so many things to get this potentially simple Query to work.

SELECT * FROM PRODUCTS
GROUP BY PRODUCTNAME order by PRODUCTNAME

[jeffstl]

Try order by multiple things

SELECT * FROM PRODUCTS
GROUP BY PRODUCTNAME order by Price asc, PRODUCTNAME

The ASC is ascending, that will list price from cheapest to expensive, and the desc is descending which will list product alphabetic

[recordscout]

Thanks for this and I have previously tried this. What this does is change the list to show the cheapest products first and not the product name first.

[jeffstl]

Well you have to have some kind of priority with the ordering.

Cheapest products first will list cheapest products first regardless of alphabetical order (but alphabetic will be a secondary ordering priority)

If you want alphabetic to be the highest priority then you just need to Order By ProductName, Price asc instead.

But you can't have both obviously. The ordering priority can only be one or the other.

[jeffstl]

In other words if product A is 10$ and Product B is 5$ Product B will be first and product A second in the Price priority.

In a alphabetic priority Product A will be first, Product B second, but then price becomes secondary in ordering.

So yeah you can't really have both as a top priority. Maybe you can look into passing a parameter that would distinguish which ordering the user wants (price or product).

[recordscout]

As a seriously messy workaround I had one statement selection the distinct product names and listing them in order, then another one taking the product name and using this reference to select the price. It works, but very slow!

I think I could use INNER JOIN if it was 2 tables, but it is just the one I don't think you can use JOIN on one table?

I have found a lot of people online asking similar questions, but all the answers seem to point to 2 tables. Surely there must be a way?

[jeffstl]

Nooooot really. At least unless I am misunderstanding what you are saying. The problem here is not limitations of programming or language, etc. But of the actual physical limitations of not being able to order things by 2 factors when 1 factors priority will always screw it up....

In other words, if AProduct's price is way more expensive then BProduct's price....and you order by Price as top priority...you HAVE to put BProduct first in the order....because its cheaper. How can you still list it alphabetically if you want the price to be the key factor in listing?

Look at the below..


orders alphabetically and gives price 2nd priority
AProduct 10$
BPRoduct 30$
CProduct-2z 1$
CProduct 5$
CProduct-2 8$
CProduct-2b 10%
DProduct 5$
EProduct 8$
FProduct 1$

orders by cheapest and gives alphabetic second priority
1$ - CProduct-2z
1$ - FProduct
5$ - CProduct
8$ - CProduct-2
8$ - EProduct
10$ - AProduct

Notice particularly CProduct-2 and CProduct-2z in both lists. That is how priority sorting works. You can't really get around it :(

[jeffstl]

Honestly I think your best bet is to have a check box or drop down that says "Order by" and have the option's be Cheapest price, or product. You can't really order by "both" with a single output table no matter what joins you use.......because if ZProduct is 1$ how can you put that at the bottom if you want cheapest at the top?

..so it goes to the top if you prioritize by Price. If there is a ZProduct for 1$ and a AProduct for 1$ and you secondary prioritize by Product Name....then AProduct will come first followed by ZProduct ...but BOTH of them will be at the top because they are 1$

You have to order by one or the other (giving the other a secondary priority if you wish)

[recordscout]

OK - I will try and explain the reason it is required.

ProductA (black) = $20
ProductA (white) = $40
ProductA (green) = $45
ProductA (silver) = $5

ProductB (white) = $15
ProductB (green) = $80
ProductB (silver) = $120
ProductB (brown) = $10

ProductC (white) = $10
ProductC (green) = $5
ProductC (silver) = $69

I have a list that requires the products to be in order, e.g. ProductA, ProductB but with each product showing the cheapest Price.

So the required result will be:

ProductA (silver) = $5
ProductB (brown) = $10
ProductC (green) = $5

The products need to be in order for browsing, but show the cheaper option first.

[jeffstl]

Hmm. Ok so you actually have groups of products that you want grouped up together by cheapest price.....

Thats a little more complicated and could involve more criteria or ranges of what "cheap" actually means.....

You could try defining your "cheap" range somehow and get all the products from each group by that range ....then order by Product

The below would get you exactly what you listed and any other product thats less then 10$

Select ProductName,ProductPrice from Products where Price <= 10 order by ProductName, ProductPrice asc
 

Obviously that might not be what you want, but its essentially the fastest way I can think of.

Otherwise you would need to just do it without the Price criteria

Notice in what you posted its 5$ first, then 10$ ..then 5$ again.....this is a secondary priority sorting for price.....because both the 5$ are not at the top.....but the products are in order A,B,C..which is the top priority sorting...

So the below here will give you results exactly like that...except that keep in mind you will still have higher prices up at the top to give way for the product name ordering......so unless you want to provide a cut off for what is considered a "Cheap" price thats all you can really do......unless you want to start talking about adding new columns to your tables that identify products as being "cheap" with a flag or something.

Select ProductName,ProductPrice from Products order by ProductName, ProductPrice asc
 

[recordscout]

Thank you for your help on this!
Unfortunatley the products are not in any order - I just wrote them like that.

If it is not possible then I will have to live with it!

From the MYSQL site I have tried things like:

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
-> FROM student
-> GROUP BY student_name;

with no luck. Also - this one looked like it could work (even though I didn't actually understand it)

mysql> SELECT student_name,
-> GROUP_CONCAT(DISTINCT test_score
-> ORDER BY test_score DESC SEPARATOR ' ')
-> FROM student
-> GROUP BY student_name;


But this gave me the same result.

I have just found this:

select id,url,source,amount from offers where price in (select min(price) from offers group by source)

on a forum. Looks interesting but I can't yet get it working.

[jeffstl]

Yeah. This one here:

select id,url,source,amount from offers where price in (select min(price) from offers group by source)

That's just another way of doing what I was saying about defining what "cheap" means. That one above is selecting things from offers where price is equal to the minimum price of the entire table.

So if you did something like above you would get all products listed that were equal to the cheapest price of your products table, but nothing else.

So yeah, using separate queries where you separate the cheapest from everything else would work too. Then your second query would just exclude the cheap ones like this:

select id,url,source,amount from offers where price not in (select min(price) from offers group by source)

[VipinKB]

Not sure, if you still require this. But, here's is the quick and simple SQL to achieve your desired results -

Select Product_Name, Min(Product_Price)
From Table_Name
Group By Product_Name
Order By Product_Name

This will give you your desired results -

ProductA (silver) = $5
ProductB (brown) = $10
ProductC (green) = $5

Hope this helps !!!

Vipin

[recordscout]

Thank you... I will check this out right away and report back!

[ssss111]

I found solution for something similar to the above check it out here http://www.anilsagar.com/blog/how-qu...more-one-field

[recordscout]

OK - Got there in the end and many thanks for all the links and examples:
http://www.artfulsoftware.com/infotr...p?&bw=1280#101

They managed to write the problem in words I could not manage!

You can see my original problem here: http://www.sunglasses-shop.co.uk/ray-ban-sunglasses.asp (not currently fixed) - but for example if you look at product code on the first page 2156 it will show the product added most recently and not the cheapest!

[recordscout]

OK - Managed to sort something out that will fix it for me at least. I tried the statements above and they were at least twice as slow to execute.

However I sorted something I am please with!

SELECT *,MIN(Price) as MINPRICE FROM products WHERE (STATEMENT HERE) GROUP BY NAME order by ORDER

What I have found this does and nice and quick is select the price as MINPRICE that I can use along with all the other data in the table. The fact the correct image and price may not show together does not matter to me.

Thanks for all the help. I tried them all!