SQL Max Value But Also Displaying Values From Other Columns

Question

I have data being pulled from a few different tables to give me the below sample data. The sample data is just showing one OrderID for simplicity. What I need to do is get the line with the max price for the OrderID, but I also need to see the other ID's associated with that specific line, as can be seen in the desired result table below. Is there any way I can achieve this?

I'm creating a view in SQL of this data, and if it's possible I'd like to do it all in one view.

Current Code

SELECT        Items.OrderID, Items.ItemID, Items.Code, Price.Price
FROM            Items LEFT OUTER JOIN
                         Codes ON Items.Code = Codes.Code LEFT OUTER JOIN
                         ItemCategories ON Codes.CategoryID = ItemCategories.CategoryID LEFT OUTER JOIN
                         Price ON Codes.CodeGrouping = Price.CodeGrouping
WHERE        (YEAR(Items.Date) = YEAR(GETDATE()) OR
                         YEAR(Items.Date) = YEAR(GETDATE()) - 1) AND (ItemCategories.CategoryID = 1) AND (Items.OrderID_Status = 3)

Sample Data

OrderID     ItemID     Code     Price
--------------------------------------
454646      429264     12121    833
454646      429677     6116     1
454646      431168     194      719
454646      431248     6078     1
454646      432023     455      1030

Desired Result

OrderID     ItemID     Code     Max_Price
-----------------------------------------------------
454646      432023     455      1030

Thanks in advance!

Answer 1

I would recommend row_number(), because it is easy to integrate it in an existing query:

select *
from (
    select 
        t.*, 
        row_number() over(partition by orderid order by price desc) rn
    from mytable t
) t
where rn = 1

In your original query:

SELECT *
FROM (
    SELECT        
        i.OrderID, 
        i.ItemID, 
        i.Code, 
        p.Price,
        ROW_NUMBER() OVER(PARTITION BY i.OrderID ORDER BY p.Price DESC) rn
    FROM Items i
    LEFT JOIN Codes c           ON i.Code = c.Code 
    LEFT JOIN ItemCategories ic ON c.CategoryID = ic.CategoryID 
    LEFT JOIN Price p           ON c.CodeGrouping = p.CodeGrouping
    WHERE
        i.Date >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1)
        AND i.Date < DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1)
        AND ic.CategoryID IN (1, 3)
) t
WHERE rn = 1

Note that I changed the query a little:

• the filtering on the dates can be optimized - do not apply date functions on the date columns (it precludes an existing index on the column)

• table aliases make the query easier to write and read