SQL Query: get the date and customerid of the most expensive order placed

Can someone help me with a mySQL query? My data is:

| OrderID | ItemPrice |
|---------|-----------|
|       1 |      2500 |
|       2 |       500 |

| OrderID | CustomerID |    OrderDate |
|---------|------------|--------------|
|       1 |          1 | 30-June-2017 |
|       2 |          1 |  2-July-2017 |

| FirstName | CustomerID |
|-----------|------------|
|      John |          1 |
|      Jane |          2 |

I’m trying to get the date and customerid of the most expensive order placed.

I can do parts of it, but am just interested in what the most efficient query would be.

In my efforts to do this, I’ve gotten this far:

I can get the orderID for the highest price with this query:

SELECT OrderID from `LineItem` ORDER BY ItemPrice DESC LIMIT 1;

Lets say Is ave the value of the above query as ‘x’, I could get the name with:

SELECT FirstName from `Order` o, `LineItem` l, `Customer` c where o.OrderID = x and o.CustomerID = c.CustomerID;

But I’m not sure how the chain the two commands together, or if this approach would be the most efficient option.

Answer

you can use subqueries.

select customerid, orderdate
from lineitem l
inner join customner c
  on l.CustomerID = c.CustomerID;
where orderid in (SELECT OrderID from `LineItem` ORDER BY ItemPrice DESC LIMIT 1)
            ^^^^^^

as you can see, your first statement is subquery for orderId

Advertisement

Answer