Second highest value in SQL Server

“Second biggest order” is a ranking problem. OFFSET 1 FETCH 1 is the short version. ROW_NUMBER is the one you can extend to fifth, or per customer.

One number

SELECT DISTINCT total_amount
FROM Orders
ORDER BY total_amount DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

The whole row

WITH ranked AS (
  SELECT order_id, customer_id, total_amount,
         DENSE_RANK() OVER (ORDER BY total_amount DESC) AS rnk
  FROM Orders
)
SELECT order_id, customer_id, total_amount
FROM ranked
WHERE rnk = 2;

DENSE_RANK so a tie for first still gives you a second place. ROW_NUMBER would skip one of the tied firsts and call the next row 2.