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

From stackoverflow

• 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
  

  Dent Argue : Thanks! With a little modification of your first select I now get cart-id and its respective item-id. I guess the results then have to be joined with the values table to get the actual value too. Right? Anyways, thanks a lot! It also opened my eyes for partion by etc :)

  Quassnoi : No, you don't have to join, I just missed ItemValues.* in the first subquery. See updated post.

  Dent Argue : Great stuff, thanks!

  Byron Whitlock : Thank you! Works like a charm!