CODEWITHSUNDEEP
mysqlsqljoinmax
Villager
Villager

Reputation: 89

MySql: Returning MAX Value in joined tables

I've got 3 tables joined here. There's the Customers, Orders, and Items ordered by Customers. These are named: CustomersTbl, OrdersTbl, OrderedItemTbl.

I'm arranging all the Orders made with their total price, and displaying the customer who made the order's name.

select * from productstbl;
select * from ordereditemtbl;
select * from orderstbl;

select 
    ordereditemtbl.ItemID,
    customerstbl.CustomerID,
    customerstbl.FirstName,
    orderstbl.OrderID,
    orderstbl.OrderDate,
    sum(ordereditemtbl.amount * productstbl.Price) as totalPrice
from
    ordereditemtbl
        inner join
    ProductsTbl ON ordereditemtbl.productID = productstbl.productID
        inner join
    orderstbl ON ordereditemtbl.orderID = ordereditemtbl.orderID
        inner join
    customerstbl ON customerstbl.customerID = orderstbl.customerID#'2'
group by ordereditemTbl.OrderID
order by totalPrice desc;

Now I'd like to display the Order with the highest TotalPrice alone. How can I get the max value of it? Is there a way to possibly use the MAX() function?

Upvotes: 0

Views: 83

Answers (2)

Villager
Villager

Reputation: 89

As suggested by abl, I have added the following at the end of the code:

order by totalPrice desc LIMIT 1;

Shows the first row of the table created, as it's shown in descending values. Thanks!

Upvotes: 0

Ram RS
Ram RS

Reputation: 277

Let's see. Check out if this works:

SELECT * FROM 
(
select 
    ordereditemtbl.ItemID,
    customerstbl.CustomerID,
    customerstbl.FirstName,
    orderstbl.OrderID,
    orderstbl.OrderDate,
    sum(ordereditemtbl.amount * productstbl.Price) as totalPrice,
    @rownum AS row_number
from
    ordereditemtbl
        inner join
    ProductsTbl ON ordereditemtbl.productID = productstbl.productID
        inner join
    orderstbl ON ordereditemtbl.orderID = ordereditemtbl.orderID
        inner join
    customerstbl ON customerstbl.customerID = orderstbl.customerID#'2'
        inner join
    (SELECT @rownum := 0) dummy
group by ordereditemTbl.OrderID
ORDER BY totalPrice desc;
)
WHERE row_number=0

This allots a row number and picks the top row.

Cheers, Ram

Upvotes: 1

Related Questions