可视化具有最高值的订单的所有产品，以从中提取最大的金额和订单

Question

​

 Desired result
    NAME                  PRICE
Lenovo X1 Carbon           4009.00
Lenovo ThinkVision X1    1549.00
Lenovo YOGA 520          1349.00
Motorola Moto Z2           999.00
Motorola Moto Z          549.00
Motorola Moto E5          179.00

​

​

ID  CATEGORY    NAME                            PRICE   ORDER_ID
1   PC          Lenovo ThinkaPad L380            1579     5
2   Mobile      Motorola Moto E5                 179      1
3   Mobile      Motorola Moto Z                549    2
4   Monitor     Lenovo ThinkVision X1            1549   4
5   PC          Lenovo X1 Carbon                 4009     3
6   Mobile      Motorola Moto Z2                 999      4
7   PC          Lenovo Legion Y530             2099   4
8   PC          Lenovo YOGA 520                 1349    3
9   Monitor     Lenovo ThinkVision X1            1549   6
10 PC           Lenovo YOGA 520                1349     6
11  Monitor   Lenovo ThinkVision X1         1549      3
12  Mobile    Motorola Moto Z2               999      3
13  Mobile    Motorola Moto E5               179      3
14  Mobile    Motorola Moto Z                   549   3

​

​

​

我使用这两个查询从照片中的表中提取信息，但我需要找到一种方法来检索信息，只需一个查询，请帮助

SELECT TOP 1 NAME , PRICE, ORDER_ID 
FROM PRODUCTS
ORDER BY PRICE DESC

SELECT NAME ,PRICE
FROM PRODUCTS 
WHERE  ORDER_ID like '%3%'  
ORDER BY PRICE DESC

期望的结果

Answer 1

-- Get everything from the table 
SELECT 
  *
FROM
   Products

-- Where the total order value for the order is the highest
Where  
   order_id = 
(
    -- The largest order total
    SELECT TOP 1
        order_id
    FROM 
        Products 
    GROUP BY 
        order_id 
    ORDER BY 
        SUM(price) DESC
)
ORDER BY 
   Price DESC 

Answer 2

SELECT NAME ,PRICE
FROM PRODUCTS 
WHERE ORDER_ID =
(     
    SELECT TOP 1 ORDER_ID   
    FROM PRODUCTS
    ORDER BY PRICE DESC
)