SQL Server: Min and Max in a single query

I needed a query in SQL Server, to result in maximum value from one column and mimimum value from another column by grouping few columns. Below example will explain better.

TableName: minmaxtest
Required    : Unique product list with latest PO_DATE and Least COST. Meaning max([PO_DATE]) and min([COST])

In the below example there are two products with different PO_DATE with various prices. PO_DATE is Purchase date. I needed 'ID' number 3 and 7 to be filtered, as they have latest PO_DATE and Least cost.

To create the same table click the link below

show script/code

create Table minmaxtest ( ID int identity(1,1) Primary Key, ProductManufacturer varchar(100), ProductID Varchar(100), ProductDescription Varchar(100), PO_DATE date, cost Money ) insert into minmaxtest (ProductManufacturer, ProductID,ProductDescription, PO_DATE, cost) select 'abc','123','pencil','2015-09-30',9 UNION -- Duplicate select 'abc','123','pencil','2015-09-30',9 UNION -- Duplicate select 'abc','123','pencil','2015-09-30',10 UNION select 'abc','123','pencil','2015-09-28',8 UNION select 'abc','123','pencil','2015-09-28',20 UNION select 'xyz','666', 'book', '2015-09-30',9 UNION select 'xyz','666', 'book', '2015-09-30',10 UNION select 'xyz','666', 'book', '2015-09-28',8 UNION select 'xyz','666', 'book', '2015-09-28',20 select * from minmaxtest
close

All new SQL developers will definitely think of this below query, which is absolutely wrong. It is not ideal to use both min and max in a single aggregate query

select ProductManufacturer,ProductID,ProductDescription,
max([PO_DATE]) As [Latest_PODate],    -- Latest PO_DATE
min([cost]) As LeastCost            -- Min Cost
from
minmaxtest Group by ProductManufacturer,ProductID,ProductDescription

This query populates incorrect results as shown below. For the dates 2015-09-30 lines, there is no associated cost with 8.00.

The right query is:

;WITH CTE
AS
(
  SELECT ROW_NUMBER() OVER (PARTITION BY ProductManufacturer,ProductID,ProductDescription ORDER BY [PO_DATE] DESC,[cost] ASC) AS sequence_id,
    *  FROM minmaxtest
)
SELECT * FROM CTE WHERE sequence_id = 1