I need help showing the most expensive order per year

Question

I need help showing the most expensive order per year. I am working on the AdventureWorks database. So far I have this, but I need only 4 result (2011,2012,2013,2014) but I cant figure out how to get the most expensive order for each year.

My code is:

select year(h.OrderDate) as "Years", 
       h.SalesOrderID, 
       p.FirstName, 
       p.LastName,  
       max(d.LineTotal)  as "Total"
from sales.SalesOrderHeader h join Person.Person p
on h.CustomerID = p.BusinessEntityID 
join sales.SalesOrderDetail d 
on d.SalesOrderID = h.SalesOrderID
group by year(h.OrderDate), h.SalesOrderID, p.FirstName, p.LastName, d.LineTotal, d.LineTotal
order by years

The rule for this question is that I need to write a query that shows the purchase amount in the most expensive order each year, showing which customers these orders belong to.

I need to use order date year, order number, last name and first name of a customer, and a Total column based on calculation UnitPrice * (1- UnitPriceDiscount) * OrderQty). I can also use LineTotal.

The final result must look like this: https://i.stack.imgur.com/chlfs.jpg

Thank you in advance!

Edit:

WITH cte AS
(
   SELECT h.SalesOrderID,
          year(h.OrderDate) as "Year",
          p.firstname,
          p.lastname,
          h.subtotal as "Total",
         ROW_NUMBER() OVER (PARTITION BY year(OrderDate) order by h.subtotal desc)  AS rn
   from sales.SalesOrderHeader h join Person.Person p
on h.CustomerID = p.BusinessEntityID 
join sales.SalesOrderDetail d 
on d.SalesOrderID = h.SalesOrderID
)
SELECT *
FROM cte
WHERE rn = 1

SELECT  *
FROM    
        (
            SELECT  h.SalesOrderID,
                    Year(h.OrderDate) as TheYear,
                    p.FirstName,
                    p.LastName,
                    h.subtotal as "Total",
                    ROW_NUMBER() OVER (PARTITION BY Year(h.OrderDate) order by h.subtotal desc) rn
            from sales.SalesOrderHeader h join Person.Person p
            on h.CustomerID = p.BusinessEntityID 
        ) s
WHERE   rn =1
ORDER   BY TheYear

Reference:

select subtotal
from sales.SalesOrderHeader
order by subtotal desc

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Jul 15 '21 at 16:52
@Larnu I saw this post before posting. I tried using this method, but idk if it's right/I got it right. Here is what I did https://imgur.com/EXej75o — Ajsrise, Jul 15 '21 at 17:07
Images of code are *not* helpful... Please use the [edit] feature. — Thom A, Jul 15 '21 at 17:57
If you want the most expensive order by year then your `PARTITION BY` and `ORDER BY` make no sense here then. If you want the most expensive you need to order by your price column (descending) and you need to partition by your year column (or year part of your date column). — Thom A, Jul 15 '21 at 18:06
@Larnu Thanks for clearing up the partition by and order by! I am now able to answer the question. Thank you! — Ajsrise, Jul 15 '21 at 18:21
@Larnu Ok, I am almost there. I've updated my post with my current answers. if you look and the image on how the final result should look like, you can see the total is way higher than my result gives. I added a reference to show that the result in the final result image comes from the subtotal category in the SalesOrderHeader folder. Why is my result ( called h.subtotal as "Total") showing smaller numbers compared to the reference? ty! — Ajsrise, Jul 15 '21 at 18:51
@Larnu Here is another image to clear it up (not code, the results from my code vs the reference): https://imgur.com/fNMdtfz — Ajsrise, Jul 15 '21 at 18:56
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) Just don't get what the difference is — Charlieface, Jul 15 '21 at 19:39
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jul 15 '21 at 20:11

score 0 · Accepted Answer · answered Jul 15 '21 at 20:26

0

Try to use over clause and partition by year. It will help you get desired result.

select year(h.OrderDate) as "Years", 
       h.SalesOrderID, 
       p.FirstName, 
       p.LastName,  
       **max(d.LineTotal) over (partition by year(h.OrderDate) order by year(h.OrderDate))** as "Total"
from sales.SalesOrderHeader h join Person.Person p
on h.CustomerID = p.BusinessEntityID 
join sales.SalesOrderDetail d 
on d.SalesOrderID = h.SalesOrderID

answered Jul 15 '21 at 20:26

Vikram

16
3

I don't get the result I am supposed to get like in the image of the final result that i posted ( most expensive order per year) – Ajsrise Jul 15 '21 at 20:45

I need help showing the most expensive order per year

1 Answers1