MySQL – How to Select a Distinct Row

greatest-n-per-groupMySQL

I have a MySQL table:

o_id | Salesperson |   fax   | Pincode  
1      Sandy         2110271   12011
2      Sandy         2452356   24523
3      Ricky         1023564   45510

I need to get the following result:

Salesperson |   fax   | Pincode  
Sandy         2452356   24523
Ricky         1023564   45510

(Note: I don't need o_id column to be displayed)

I have tried:

select distinct salesperson,faxno,pin 
from OPPORTUNITY

and:

select salesperson,faxno,pincode 
from OPPORTUNITY 
Group By (salesperson)

…but still I am getting:

Salesperson |   fax   | Pincode  
Sandy         2110271   12011
Ricky         1023564   45510

I have tried a lot of scenarios present on the web but still not able to achieve the expected result.

Is there any property or function that will give "Sandy" – whose o_id is max.

Example:

Select distinct sales, faxno, pincode 
from opportunity where o_id= max(o_id)

Although above code is not working, but anything like that can help?

Best Answer

The row:

Sandy         2452356   24523

is preferred because it has the largest o_id. You can pick the largest o_id per salesperson via:

select max(o_id), salesperson
from T 
group by salesperson

An important property of SQL is that it is closed, i.e. a result of a query is a relation (table). So we can simply join this with the original table:

select x.o_id, x.Salesperson, x.fax, x.Pincode
from T as x
join (
    select max(o_id) as o_id, salesperson
    from T
    group by salesperson
) as y
    on x.o_id = y.o_id
   and x.salesperson = y.salesperson

Even if question does not say so explicitly, there is reason to believe that salesperson is functionally dependent of o_id. If that is the case it is sufficient to join by o_id alone:

select x.o_id, x.Salesperson, x.fax, x.Pincode
from T as x
join (
    select max(o_id) as o_id, salesperson
    from T
    group by salesperson
) as y
    on x.o_id = y.o_id

In SQL2003, window functions were introduced. They are a perfect fit for this kind of query. I have heard the next version of MySQL will support them, but I guess you are not using that.

You also mention this construction:

select salesperson,faxno,pincode 
from OPPORTUNITY 
Group By salesperson

It is probably the construction in MySQL that causes the most confusion. It is invalid in most (all?) other products, because the result is non-deterministic or random. What happens is that MySQL randomly picks a faxno, pincode for each salesperson.

Related Question