SQL MAX of column including its primary key

问题:

Short:

From below sql select I get the cart_id and the value of the maximum valued item in that cart.

SELECT CartItems.cart_id, MAX(ItemValues.value)
FROM CartItems 
INNER JOIN ItemValues 
ON CartItems.item_id=ItemValues.item_id
GROUP BY CartItems.cart_id

but I also need item_id for that item (ItemValues.item-id).

Long:

Two tables, CartItems, ItemValues (and their respective Carts, Items, irrelevant here).
Each cart can have several items wheras each item has one value defined in ItemValues.
Each item belongs to one cart.
The value of a cart is the value of the item with maximum value within its cart.
How do I select cart-id, max(item-value) and it's corresponding item-id?

For instance cart-id A contains item-id X with value 10 and item-id Y with value 90.
With above sql select I get,

A, 90

What I need is

A, Y, 90

platform: MS SQL

回答1:

In MS SQL and Oracle:

SELECT *
FROM
  (
  SELECT ci.*, iv.*, 
        ROW_NUMBER() OVER (PARTITION BY CartItems.cart_id ORDER BY ItemValues.value DESC)
  FROM   CartItems ci
  INNER JOIN ItemValues iv
     ON CartItems.item_id=ItemValues.item_id
  ) s
WHERE rn = 1

In MySQL:

SELECT
FROM
  (
  SELECT ci.*,
         (
         SELECT id
         FROM ItemValues iv
         WHERE iv.item_id = ci.item_id
         ORDER BY
               value DESC
         LIMIT 1
         ) AS maxitem
  FROM   CartItems ci
  ) iv, ItemValues ivo
WHERE ivo.id = iv.maxitem

回答2:

This code was written for Oracle, but should be compatible with most SQL versions:

This gets the max(high_val) and returns its key.

select high_val, my_key
from (select high_val, my_key
      from mytable
      where something = 'avalue'
      order by high_val desc)
where rownum <= 1

What this says is: Sort mytable by high_val descending for values where something = 'avalue'. Only grab the top row, which will provide you with the max(high_val) in the selected range and the my_key to that table.