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 misunderstandin g 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....becaus e 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.......becau se 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.....becau se 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......s o 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(DI STINCT 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,a mount 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,a mount 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,a mount 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_Pri ce)
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]

Possible Solution

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