Select max value from different tables in mysql

Question

I want to select the most expensive product each customer bought, but I have the information in 3 tables: Customers, Purchases, ProductsPrices.

Tables look like:

Customers:

Customer_ID | Customer_Name

Purchases:

Customer_ID | Product_ID

ProductPrices:

Product_ID | Price

What i'm running is:

SELECT  
Customer_Name, max(Price), Purchases.Product_ID
            FROM Customers 
            LEFT JOIN Purchases 
            ON Customers.Customer_ID=Purchases.Customer_ID
            LEFT JOIN ProductPrices
            ON Purchases.Product_ID=ProductPrices.Product_ID
                        GROUP BY Customer_Name
                        ORDER BY ABS(Price) DESC
                        ;

And the output i'm getting is the names and the highest purchase correct, but the product_id is the first, and not associated with the highest price.

Can you help me to spot what am I doing wrong?

EDIT:

To make it easier for you, I created this:

http://sqlfiddle.com/#!2/db7f9/1

here: http://sqlfiddle.com/#!2/db7f9/1 – Piero Oct 12 '13 at 11:24 — Piero, Oct 12 '13 at 11:24

geomagas · Answer 1 · 2013-10-12T11:36:31.663

Try this:

select distinct c.Customer_Name,pp.Product_Id,m.Price
from 
  (select Customer_ID,max(Price) as Price
  from Purchases p join ProductPrices pp on (p.Product_ID=pp.Product_ID)
  group by Customer_ID) m
join Customers c on (m.Customer_ID=c.Customer_ID)
join ProductPrices pp on (pp.Price=m.Price)
join Purchases p on (m.Customer_ID=p.Customer_ID and p.Product_ID=pp.Product_ID)

Note: If a customer purchased multiple products with the same price, this will give you muliple rows per Customer.

score -1 · Accepted Answer · answered Oct 12 '13 at 11:46

-1

try this

    SELECT Customer_Name, max(Price) price , Product_ID FROM (
 SELECT  
Customer_Name, Price, Purchases.Product_ID
        FROM Customers 
        INNER JOIN Purchases 
        ON Customers.Customer_ID=Purchases.Customer_ID
        INNER JOIN ProductPrices
        ON Purchases.Product_ID=ProductPrices.Product_ID


                    ORDER BY ABS(Price) DESC
)t

GROUP BY Customer_Name

DEMO HERE

OUTPUT:

  CUSTOMER_NAME     PRICE   PRODUCT_ID
     John           30000   3
     Kate           30000   3
     Peter          20000   2

answered Oct 12 '13 at 11:46

echo_Me

37,078
5
58
78

wish i can hear what reason have downvoted me this downvoter or its just pleasure ? any explanations. this answer gives exactly what he need. – echo_Me Oct 12 '13 at 12:38
downvoter read this http://meta.stackexchange.com/questions/135/encouraging-people-to-explain-downvotes – echo_Me Oct 12 '13 at 13:23
2

I guess because it uses a MySQL non-standard SQL extension (allowing columns not in GROUP BY to appear non-aggregated in the SELECT list) and thus possibly erroneous results. – ypercubeᵀᴹ Oct 12 '13 at 21:11
The fact that it works in your machine (sorry, example) does not count for much as the **[MySQL documentation](http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html)** says: *"This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, **the result is indeterminate**."* – ypercubeᵀᴹ Oct 12 '13 at 21:15
@ypercube , well the question is helpful and it solved his problem WHERE johns answer didnt work at all and get no downvote but why i got downvote where it works and helped ? this is surely one person who want geomagas to appear with no sence reason. anyway glad i helped the OP. thanks ypercube – echo_Me Oct 13 '13 at 09:33
3

You can say it **["Works on My Machine"](http://www.codinghorror.com/blog/2007/03/the-works-on-my-machine-certification-program.html)** as many times as you want. That doesn't make this answer correct. – ypercubeᵀᴹ Oct 13 '13 at 10:06
Besides my 2 comments above, it will not work if the [ONLY_FULL_GROUP_BY](http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_only_full_group_by) is set to ON. – ypercubeᵀᴹ Oct 13 '13 at 10:08
You have a similarly wrong answer here: http://stackoverflow.com/questions/18783157/mysql-distinct-on-only-one-column-only-display-first-duplicate-row/18783605#18783605 (where I see 2 downvotes) – ypercubeᵀᴹ Oct 13 '13 at 10:13
And another, with 3 downvotes: http://stackoverflow.com/questions/17673457/mysql-select-distinct-on/17673791#17673791 – ypercubeᵀᴹ Oct 13 '13 at 10:14
2

I really don't understand what John's and geomagas's answers have to do with your wrong answer and the downvoting here. – ypercubeᵀᴹ Oct 13 '13 at 10:14
@ypercube , ok thanks, its pointless to talk i know. its not my machine its sqlfiddle , if you think sqlfiddle is not working good then its his problem. you can just test it in your machine and comeback with an answer. you dont have to answer again , just close this subkect i dont care if gets downvoted as i care it helped. you have decided to dont see what i mean just what you want say or proof. please dont answer again. i dont want talk about this. have nice day. – echo_Me Oct 13 '13 at 10:20
4

@echo_Me: Even though the technique is relatively known, **it is not backed by the manuals**, meaning the behaviour can change without a warning. Therefore, you should hardly rely on the feature yourself, much less recommend it on a public Q&A site. – Andriy M Oct 13 '13 at 11:58

Select max value from different tables in mysql

2 Answers2