select 
    c.customerName, o.product, o.productDefinition, q.orderDate 
from
   (
    select
        customerId, max(date) as orderDate 
    from 
        Order 
    group by 
        customerId 
    ) q
    join customer c on q.customerId = c.customerId 
    join order o on q.customerId = o.customerId 
        and q.orderDate = o.date

create table Customer 
(
	Customer varchar(32), 
	[Customer Id] int 
)

create table [Order] 
(
	OrderId int, 
	[CustomerId] int, 
	Product varchar(32), 
	ProductDesc varchar(32) 
)

insert into Customer (Customer, [Customer Id])	
select 'Mary', 1
union all
select 'Jhon', 2
union all
select 'Anna', 3

insert into [Order](OrderId, [CustomerId], Product, ProductDesc)
select 1, 1, 'video', 'bla bla'
union all
select 2, 1, 'tv', 'bla bala'
union all
select 3, 2, 'video', 'bla bla'
union all
select 4, 2, 'cd', 'bla bla'

select 
    c.Customer I, ISNULL(o.Product,'') want, ISNULL(o.ProductDesc,'') [to see]
from
   Customer c left join 
   (
    select
        [CustomerId], max(OrderId) as OrderId 
    from 
        [Order] 
    group by 
        [CustomerId] 
    ) q on q.[CustomerId] = c.[Customer Id]
    left join [Order] o on q.[CustomerId] = o.[CustomerId] 
        and q.OrderId = o.OrderId
order by 
	c.[Customer Id] 

I                                want                             to see                           
-------------------------------- -------------------------------- -------------------------------- 
Mary                             tv                               bla bala
Jhon                             cd                               bla bla
Anna                                                              

(3 row(s) affected)