How to select the most frequent value for each group within two tables?

Question

I have two tables and I want to select the most ordered (amount) product for each shipmentType (see result table)

shipment table

id	shipmentTyp	amount	productID
1	A	3	1
2	S	7	1
3	A	12	3
4	T	15	2
5	T	7	1
6	T	4	3
7	A	1	3
8	S	78	2

Products table

productID	productName
1	P1
2	P2
3	P3

Result table

shipmentType	productName	amount
A	P3	12
S	P2	78
T	P2	15

Answer 1

It seems a simple ROW_NUMBER() problem to me -

SELECT 
    SH.shipmentType, P.productName, SH.amount
FROM 
    (SELECT 
         shipmentType, amount, productID,
         ROW_NUMBER() OVER (PARTITION BY shipmentType ORDER BY amount DESC) RN
     FROM 
         shipment) SH
JOIN 
    Products P ON SH.productID = P.productID
WHERE 
    RN = 1;