'Select highest value from joined table

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

sqlsql-server


Solution 1:[1]

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

Solution 2:[2]

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;

Solution 3:[3]

it is better to use row_number to get highest delivery_time row. we can also order it based on highest product_id if there is more than 1 highest delivery time

SELECT ol.order_id,
       ol.product_id,
       p.deliverytime
  FROM (   
       SELECT ol.order_id,
              ol.product_id,
              p.deliverytime,
              row_number() over(partition by ol.order_id 
              order by p.deliverytime desc, ol.product_id desc) rn 
         FROM orderline ol 
         JOIN products p 
           ON ol.product_id = p.product_id 
      )RPR
 WHERE rn = 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Waldir J. Pereira Junior
Solution 3 Yusuf

Related Questions