🚀 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

Dear John Gilson,

In your posting Re: question from Sun, 31 Aug 2003 11:44:00 GMT you
write:

>
> "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


This worked for result:
1   marke1  26   mark2   24
2   fred1   19   fred2   17

How it modify it to result three highest maxes, like:
1  marke  26  mark2  24   mark3   20
2  fred1  19  fred2  17   fred3   15


 





-- 
Spam protected message from: 
Sent by  ricksql from yahoo piece of com
Posted via http://www.usenet-replayer.com/cgi/content/new

"ricksql@yahoo.com" <u50415039@spawnkill.ip-mobilphone.net> wrote in message
news:r.1062365895.1747711181@[63.127.215.130]...
> Dear John Gilson,
>
> In your posting Re: question from Sun, 31 Aug 2003 11:44:00 GMT you
> write:
>
> >
> > "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
>
>
> This worked for result:
> 1   marke1  26   mark2   24
> 2   fred1   19   fred2   17
>
> How it modify it to result three highest maxes, like:
> 1  marke  26  mark2  24   mark3   20
> 2  fred1  19  fred2  17   fred3   15
>
>
>
>
>
>
>
>
> -- 
> Spam protected message from:
> Sent by  ricksql from yahoo piece of com
> Posted via http://www.usenet-replayer.com/cgi/content/new

As I think I see where this is going, allow me to suggest a normalization
of relations.  Let's assume your data isn't in a temp table as I will be
defining a view.  Let's place your data in table T.

CREATE VIEW Rank (order_id, fname, age, rank)
AS
SELECT T1."order", T1.fname, T1.age, COUNT(DISTINCT T2.age)
FROM T AS T1
            INNER JOIN
            T AS T2
            ON T1."order" = T2."order" AND
                  T2.age >= T1.age
GROUP BY T1."order", T1.fname, T1.age

SELECT R1.order_id, R1.fname AS fname1, R1.age AS age1,
               R2.fname AS fname2, R2.age AS age2,
               R3.fname AS fname3, R3.age AS age3
FROM Rank AS R1
            LEFT OUTER JOIN
            Rank AS R2
            ON R1.order_id = R2.order_id AND
                   R2.rank = 2
            LEFT OUTER JOIN
            Rank AS R3
            ON R3.order_id = R1.order_id AND
                   R3.rank = 3
WHERE R1.rank = 1

Regards,
jag