SQL Max over multiple versions

Question

I have a table with three columns

Product        Version        Price
1              1              25
1              2              15
1              3              25
2              1              8
2              2              8
2              3              4
3              1              25
3              2              10
3              3              5

I want to get the max price and the max version by product. So in the above example the results would have product 1, version 3, price25. product 2, version 2, price 8.

Can you let me know how I would do this.

I'm on Teradata

Answer 1

If Teradata supports the ROW_NUMBER analytic function:

SELECT
  Product,
  Version,
  Price
FROM (
  SELECT
    atable.*,  /* or specify column names explicitly as necessary */
    ROW_NUMBER() OVER (PARTITION BY Product
                       ORDER BY Price DESC, Version DESC) AS rn
  FROM atable
) s
WHERE rn = 1
;