asked on

query to return max distinct rows.

I have what I hope is a simple question. I have the following query

select       c.custid,
      c.lastname,
      c.firstname,
      cl.login,
      cl.password
from customers c, cust_detail cd, ,cust_login cl
where c.custid = cd.custid
and c.custid = cl.custid
and cd.email = 'abc@123.com'

It will return something that looks like this.

custid lastname firstname login password
--------------------------------------------------------------------------
1 Turner Jack jturner abc
2 Turner Bobby bturner def
36 Turner Jack jturner ghi
41 Turnder Bobby bturner jkl

I want to limit my results to the two unique lastname + firstname combinations with the max custid. For example how I woul like the results to look is as follows.

custid lastname firstname login password
--------------------------------------------------------------------------
36 Turner Jack jturner ghi
41 Turnder Bobby bturner jkl

Thanks for you help,
Clint...

amit_g

Try this ...

select c.custid,
c.lastname,
c.firstname,
cl.login,
cl.password
from customer c
inner join cust_detail cd on c.custid = cd.custid
inner join cust_login cl on c.custid = cl.custid
inner join (select Max(custid) as MaxCustId from customers group by firstname + lastname) C1 on C.custid = C1.MaxCustId
where cd.email = 'abc@123.com'

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clintnash

ASKER

That one did it for me, Thanks again for your help Mr. F.

Clint...