question

[ricksql]

#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

[John Gilson]

"ri*****@yahoo.com" <u5*******@spawnkill.ip-mobilphone.net> wrote in message
news:l.*********************@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

[ricksql]

Dear John Gilson,

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

"ri*****@yahoo.com" <u5*******@spawnkill.ip-mobilphone.net> wrote in message
news:l.*********************@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

[John Gilson]

"ri*****@yahoo.com" <u5*******@spawnkill.ip-mobilphone.net> wrote in message
news:r.*********************@[63.127.215.130]...

Dear John Gilson,

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

"ri*****@yahoo.com" <u5*******@spawnkill.ip-mobilphone.net> wrote in message
news:l.*********************@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