🚀 go-pugleaf

"ricksql@yahoo.com" <u50415039@spawnkill.ip-mobilphone.net> wrote in message
news:l.1062309783.1757537841@host-66-81-126-37.rev.o1.com...
> #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

Posting DDL statements (CREATE TABLE statements), sample data
(in the form of INSERT statements), and the expected query result are
all important accompaniments when posing a question.

This is offered untested.

SELECT T1."order", T1.fname AS fname1, T1.age AS age1,
               T3.fname AS fname2, T3.age AS age2
FROM #temptable AS T1
            LEFT OUTER JOIN
            #temptable AS T2
            ON T1."order" = T2."order" AND
                   T2.age > T1.age
            LEFT OUTER JOIN
            #temptable AS T3
            ON T3."order" = T1."order" AND
                   T3.age < T1.age AND
                   NOT EXISTS (SELECT *
                                           FROM #temptable AS T4
                                           WHERE T4."order" = T1."order" AND
                                                          T4.age > T3.age AND
                                                          T4.age < T1.age)
WHERE T2.age IS NULL
ORDER BY T1."order", T1.age, T1.fname

Regards,
jag