select a.description, a.reference, s.date, s.price
from Article a join Sales s on a.code = s.code
join
(
select code, max(date) from sales
group by code) t
on s.code = t.code and s.date = t.date

quote:

---

Originally posted by harsh_athalye

something like this:

select a.description, a.reference, s.date, s.price
from Article a join Sales s on a.code = s.code
join
(
select code, max(date) as [date] from sales
group by code) t
on s.code = t.code and s.[date] = t.[date]

Harsh Athalye
http://www.letsgeek.net/

---

select a.description, a.reference, s.date, s.price
from Article a join Sales s on a.code = s.article
join
(
select article, max(date) as [date] from sales
group by article) t
on s.article = t.article 
and s.[date] = t.[date]

select description, reference, [date], price
from
(
select a.description, a.reference, s.[date], s.price,row_Number() over (partition by a.description order by s.date desc) as seq
from Article a join Sales s on a.code = s.article
)t
where seq=1

quote:

---

Originally posted by harsh_athalye

I liked your solution, visakh. Looks much elegant!

Harsh Athalye
http://www.letsgeek.net/

---