SQL query for finding representative rows in a table

Question

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

PaymentID INT, CustomerID INT, Value INT, PaidOn DATE

And I want to run a query 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
)

Answer 2

select PaymentID, CustomerID, Value, PaidOn
from payments
where (customerID, value) in
( select customerID, max(value)
  from payments 
  group by customerID
);

Note that this can return more than one row per customer if they have more than one payment with the maximum value.

Answer 3

One more using Self join - matter of syntactic sugar. Easier to understand and good on performance.

select PaymentID, CustomerID, Value, PaidOn
from
( select customerID, max(value) as maxValue
  from payments 
  group by customerID
)as T
 INNER JOIN payments as P
  ON P.Value=T.maxValue AND P.CustomerID = T.CustomerID

-Maulik Modi

Answer 4

Here's one option, which uses a derived table:

SELECT     p2.PaymentId, p2.CustomerID, p2.Value, p2.PaidOn
FROM         Payments AS p2 INNER JOIN
    (SELECT     CustomerID, MAX(Value) AS Value
     FROM          Payments
     GROUP BY CustomerID)
AS p1
ON p1.CustomerID = p2.CustomerID AND p1.Value = p2.Value

Answer 5

This is pretty much an identical question to this one from earlier today.

To avoid subqueries you can also use:

SELECT
     P1.PaymentID,
     P1.CustomerID,
     P1.Value,
     P1.PaidOn
FROM
     Payments P1
LEFT OUTER JOIN Payments P2 ON
     P2.CustomerID = P1.CustomerID AND
     P2.Value > P1.Value
WHERE
     P2.PaymentID IS NULL

Depending on your business rules for ties on the value you will need to alter this query.

For example, to use the latest payment if there is a tie:

SELECT
     P1.PaymentID,
     P1.CustomerID,
     P1.Value,
     P1.PaidOn
FROM
     Payments P1
LEFT OUTER JOIN Payments P2 ON
     P2.CustomerID = P1.CustomerID AND
     (P2.Value > P1.Value OR (P2.Value = P1.Value AND P2.PaidOn > P1.PaidOn))
WHERE
     P2.PaymentID IS NULL