getting the max number and show the row

Here is an AdventureWorks example to get the maximum list price products for each color:

WITH CTE AS
(SELECT RNK=DENSE_RANK() OVER (PARTITION BY Color ORDER BY ListPrice DESC),
Color, ListPrice, ProductName=Name
FROM Production.Product WHERE ProductSubcategoryID > 0 AND Color is not null)
SELECT Color, ListPrice, ProductName FROM CTE
WHERE RNK = 1 ORDER BY Color, ProductName;
/*
Black   3374.99 Mountain-100 Black, 38
Black   3374.99 Mountain-100 Black, 42
Black   3374.99 Mountain-100 Black, 44
Black   3374.99 Mountain-100 Black, 48
Blue    2384.07 Touring-1000 Blue, 46
Blue    2384.07 Touring-1000 Blue, 50
Blue    2384.07 Touring-1000 Blue, 54
Blue    2384.07 Touring-1000 Blue, 60
Grey    125.00  Touring-Panniers, Large
Multi   89.99   Men's Bib-Shorts, L
Multi   89.99   Men's Bib-Shorts, M
Multi   89.99   Men's Bib-Shorts, S
Red     3578.27 Road-150 Red, 44
Red     3578.27 Road-150 Red, 48
Red     3578.27 Road-150 Red, 52
Red     3578.27 Road-150 Red, 56
Red     3578.27 Road-150 Red, 62
Silver  3399.99 Mountain-100 Silver, 38
Silver  3399.99 Mountain-100 Silver, 42
Silver  3399.99 Mountain-100 Silver, 44
Silver  3399.99 Mountain-100 Silver, 48
Silver/Black    80.99   HL Mountain Pedal
Silver/Black    80.99   HL Road Pedal
Silver/Black    80.99   Touring Pedal
White   9.50    Mountain Bike Socks, L
White   9.50    Mountain Bike Socks, M
Yellow  2384.07 Touring-1000 Yellow, 46
Yellow  2384.07 Touring-1000 Yellow, 50
Yellow  2384.07 Touring-1000 Yellow, 54
Yellow  2384.07 Touring-1000 Yellow, 60
*/

---

---

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design

New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014