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...
LVL 1
clintnashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

amit_gCommented:
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'
rafranciscoCommented:
Try this:

select      c.custid,
     c.lastname,
     c.firstname,
     cl.login,
     cl.password
from customers c inner join  cust_detail cd
                                    on c.custid = cd.custid and
                                       cd.email = 'abc@123.com'
                          inner join cust_login cl
                                    on c.custid = cl.custid
                          inner join (select lastname, firstname, max(custid) as maxcustid
                                         from customers
                                         group by lastname, firstname) cmax
                                   on c.custid = cmax.maxcustid and c.lastname = cmax.lastname and c.firstname = cmax.firstname

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
clintnashAuthor Commented:
That one did it for me, Thanks again for your help Mr. F.

Clint...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.