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.

score 10 · Answer 1 · answered Dec 17 '08 at 17:19

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.

score 4 · Answer 2 · edited May 23 '17 at 10:27

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

A moot point now, but it's not an identical question, even though the answers are quite similar. In the one earlier today, there is not a requirement for the record to be for the maximum amount. — GlennFromIowa, Jul 16 '15 at 20:38

score 2 · Accepted Answer · answered Dec 17 '08 at 17:07

2

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
)

answered Dec 17 '08 at 17:07

RB.

36,301
12
91
131

This will probably not perform as well as Tony Andrew's, because the subquery has to be run for each customer. – recursive Dec 17 '08 at 17:25

score 0 · Answer 4 · answered Dec 17 '08 at 18:23

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

score 0 · Answer 5 · answered Dec 17 '08 at 18:24

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

SQL query for finding representative rows in a table

5 Answers5

Linked