SELECT *
FROM (
SELECT ID,
Email,
ProductName,
ProductModel,
ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
FROM Products
) a
WHERE rn = 1
WITH cte AS
( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
FROM MyTable
)
SELECT *
FROM cte
WHERE rn = 1
SELECT * FROM table
WHERE id IN (
SELECT MAX(id) FROM table GROUP BY name
)