How to select one value from a column in a group query

Question

I have 2 tables, customer and payment in my Oracle database, where a customer can have many payment.

If I run the following query:

SELECT customer.customer_id, customer.name, payment.pay_date
FROM customer, payment
WHERE customer.customer_id = payment.customer_id;

I will get the result as shown in an example below.

customer_id | name | pay_date
----------------------------------
    1001    | Mr.A | 01/10/2014
    1001    | Mr.A | 02/10/2014
    1001    | Mr.A | 03/10/2014
    1001    | Mr.A | 04/10/2014

Now, I want to select the last pay_date for each customer_id, I would do this.

SELECT customer.customer_id, customer.name, max(payment.pay_date) as last_pay_date
FROM customer, payment
WHERE customer.customer_id = payment.customer_id
GROUP BY customer.customer_id, customer.name;

So that the result becomes

customer_id | name | last_pay_date
----------------------------------
    1001    | Mr.A | 04/10/2014

The problem is that I know customer.name is not distinct for every customer with the same customer_id.

I would like to know if there is an aggregation function, says ONE(), that let me do as follows:

SELECT customer.customer_id, ONE(customer.name), max(payment.pay_date) as last_pay_date
FROM customer, payment
WHERE customer.customer_id = payment.customer_id
GROUP BY customer.customer_id;

So that I don't have to put the customer.name to the GROUP BY clause as I think it makes my SQL hard to read: it misleads anyone reading the SQL to think that the result might has more than 1 row with the same customer_id but different name.

Answer 1

You can use the FIRST() or LAST() (as of Oracle version 9i) or MIN() or MAX() aggregate function.

SELECT 
    customer.customer_id, 
    FIRST(customer.name), 
    max(payment.pay_date) as last_pay_date
FROM 
    customer JOIN payment
ON 
    customer.customer_id = payment.customer_id
GROUP BY 
    customer.customer_id;

But you cannot predict which customer name pops up...

See Manual