SQL query for finding representative rows in a table

Question

Let's say I have a payments table like so:

CREATE TABLE Payments (
  PaymentID INT,
  CustomerID INT,
  Value INT,
  PaidOn DATE
);

INSERT INTO Payments
  VALUES
(1, 1, 5, '2000-01-01'),
(2, 1, 10, '2000-02-01'),
(3, 2, 10, '2000-01-02'),
(4, 2, 10, '2000-01-20'),
(5, 2, 5, '2000-02-02'),

And I want to run a query for the entire row for the maximum payment made by each customer. Is this possible using a single SQL query, to avoid having to dig through every row that I'm not interested -- or worse, run a query for each customer?

The best I have come up with so far is:

SELECT CustomerID, MAX(Value)
  FROM Payments
  GROUP BY CustomerID;

But this does not give me the PaymentId or PaidOn values for the rows it finds.

Answer 1

The following query will accomplish that. It pulls out all the rows for which there is not a greater Value.

SELECT *
FROM payments p
WHERE NOT EXISTS (
    SELECT *
    FROM payments p2
    WHERE p2.CustomerID = p.CustomerId
    AND   p2.Value > p.Value
)