How to GROUP BY one column and ORDER BY another in T-SQL

Question

I have a table of items with some values, among them cost and purchase date. I'm trying to get a list of the most expensive items, one per item type, ordered by the purchase date of that specific item, without the purchase date in the results.

My table (simplified):

CREATE TABLE Purchases
    (ItemType varchar(25),
    Cost int,
    PurchaseDate smalldatetime)

My sample data:

INSERT INTO Purchases VALUES
    ('Hat',     0,      '2007-05-20 15:22'),
    ('Hat',     0,      '2007-07-01 15:00'),
    ('Shirt',   3500,   '2007-07-30 08:43'),
    ('Pants',   2000,   '2008-07-30 12:00'),
    ('Pants',   4000,   '2009-03-15 07:30'),
    ('Sweater', 3000,   '2011-05-20 15:22'),
    ('Sweater', 3750,   '2012-07-01 22:00'),
    ('Sweater', 2700,   '2014-06-12 11:00'),
    ('Hat',     4700,   '2015-06-29 07:10')

My expected output (dates added for clarity):

ItemType                MostExpensivePerType
------------------------- --------------------
Shirt                     3500                (2007-07-30 08:43)
Pants                     4000                (2009-03-15 07:30)
Sweater                   3750                (2012-07-01 22:00)
Hat                       4700                (2015-06-29 07:10)

My work so far:

I've tried things back and forth, and my best result is with this query:

SELECT 
    ItemType, MAX(Cost) AS MostExpensivePerType 
FROM 
    Purchases 
GROUP BY 
    ItemType 
ORDER BY 
    MostExpensivePerType DESC

Which yields the most expensive items per item type, but orders them by cost. Without the ORDER BY clause, they seem to be ordered alphabetically. I realize that I need the date column in my query as well, but can I enter it and 'hide' it in the results? Or do I need to save the results I have so far in a temporary table and join with my regular table? What is the best way to go about this?

SQL Fiddle here!

Answer 1

Use window functions:

select ItemType, Cost MostExpensivePerType
from (select p.*,
             row_number() over (partition by itemtype order by cost desc) as seqnum
      from purchases p
     ) t
where seqnum = 1
order by PurchaseDate;

SQLFiddle here.