Select highest value from joined table-CodePudding

I need to select a distinct row based on a value from a joined table in SQL Server.

Table Orderlines:

| order_id   |  product_id|
|------------|------------|
|       1234 |         11 |
|       1234 |         22 |
|       1234 |         33 |
|       1234 |         44 |
|       1234 |         55 |
|       2222 |         66 |
|       2222 |         77 |

Table Products:

| product_id |  deliverytime|
|------------|--------------|
|         11 |            2 |
|         22 |            3 |
|         33 |            5 |
|         44 |            2 |
|         55 |            1 |
|         66 |            4 |
|         77 |            1 |

Result I am looking for:

| order_id   |  product_id|  deliverytime|
|------------|------------|--------------|
|       1234 |         33 |            5 |
|       2222 |         66 |            4 |

Thanks in advance

CodePudding user response：

We can RANK by deliverytime DESC in a CTE and then only take RANK 1 which is the highest value.

WITH CTE AS
(SELECT 
  o.product_id,
  o.order_id
  p.deliverytime,
  RANK() OVER (PARTITION BY order_id 
       ORDER BY deliverytime DESC) rn 
  FROM Orderline o
  JOIN Products p 
  ON o.product_id = p.product_id )
  
SELECT
 order_id,
 product_id,
 deliverytime
FROM CTE
WHERE rn = 1;
ORDER BY order_id

CodePudding user response：

Maybe it should work for you, but if there are two or more products with the same highest value, you'd get more than 1 row per order:

select v.order_id
    , p2.product_id
    , p2.deliverytime
from (
        select o.order_id
            , max(p.deliverytime) as max_deliverytime
        from Orderlines o
            join Products p
                on o.product_id = p.product_id 
        group by o.order_id
    ) v
    join Products p2
        on v.max_deliverytime = p2.deliverytime;