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 Ite

Solution 1:

In MS SQL and Oracle:

SELECT*FROM
  (
  SELECT ci.*, iv.*, 
        ROW_NUMBER() OVER (PARTITIONBY CartItems.cart_id ORDERBY ItemValues.value DESC)
  FROM   CartItems ci
  INNERJOIN ItemValues iv
     ON CartItems.item_id=ItemValues.item_id
  ) s
WHERE rn =1

In MySQL:

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

Solution 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'orderby 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.