SQL GROUP BY one field and list latest value of two other fields at the same time

Take in consideration this data:

id      firstname   lastname    registration_date
101126423 开发者_StackOverflow中文版  foo         bar     2010-06-17 13:31:00.000
101126423   foo         bar     2010-06-17 13:31:00.000
101126423   foo         bar jr  2010-06-18 12:13:00.000
101152718   john        doe     2010-02-26 19:08:00.000
101152718   john        doe     2010-02-26 19:08:00.000
101152718   john        doe     2010-02-26 19:08:00.000

You can have customers with with the same id but with a different firstname / lastname! I want to get all distinct ids but with the latest firstname/lastname (based on registration_date).

For my example I would get:

id      firstname   lastname
101126423   foo         bar jr
101152718   john        doe

So far I got:

SELECT DISTINCT id, firstname, lastname
FROM member

but it's obviously not working... I've tried other queries with no success so far. Maybe having can help me but I never used it...

I use SQL Server 2008 in this project.

A couple options for you:

Option 1:

;with cte as(
  select id, max(registration_date) lastReg
  from member
  group by id
)
select distinct m.id, m.firstname, m.lastname
from member m
  join cte c on m.id=c.id
            and m.registration_date = c.lastReg

Option 2:

;with cte as(
  select id, firstname, lastname,
         row_number() over(partition by id order by registration_date desc) as 'order'
  from member
)
select id, firstname, lastname
from cte
where order = 1

The biggest difference in the two, with regards to their results, is how they handle the case where the most recent registration time is duplicated for an id with multiple names. In this case, Option 1 will return both names that have the latest registration date and Option 2 will only return one (randomly). An example of this case is (a slight tweak of your sample data):

id          firstname   lastname    registration_date
101126423   foo         bar         2010-06-17 13:31:00.000
101126423   foo         bar         2010-06-18 12:13:00.000
101126423   foo         bar jr      2010-06-18 12:13:00.000
101152718   john        doe         2010-02-26 19:08:00.000
101152718   john        doe         2010-02-26 19:08:00.000
101152718   john        doe         2010-02-26 19:08:00.000

--Option 1 result:
id          firstname   lastname
101126423   foo         bar
101126423   foo         bar jr
101152718   john        doe

--Option 2 result (possibility 1):
id          firstname   lastname
101126423   foo         bar
101152718   john        doe

--Option 2 result (possibility 2):
id          firstname   lastname
101126423   foo         bar jr
101152718   john        doe