#temptable got order,fname and age.
trying to find two high maxes per each order.
query returns (1) but (2) is correct answer.
supposedly, max(age2) < max(age1).
****
select d.order,case d.t when 1 then s.fname else null end as fname,
case d.t when 1 then s.age else null end as age1,
case d.t when 2 then s.fname else null end as fname,
case d.t when 2 then s.age else null end as age2
from #temptable s
join ( select order, max(age) age, 1 t
from #temptable group by order
union all select order, max(age) age, 2 t
from #temptable group by order) d on d.order = s.order
and s.age = d.age
group by d.order, d.t,s.fname,s.age
****

(1)
1 mark1 26
1 mark1 26
2 fred1 19
2 fred1 19


(2)
1 marke1 26 mark2 24
2 fred1 19 fred2 17






--
Sent by ricksql from yahoo in area com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new