Complex query without PL/SQL 1

[mrcomino]

I have a table with "TEAMS" and "POSITIONS_IN_A_RACE"
Example

TEAM POSITION
---- --------
A 1
B 2
A 3
F 4
A 5
C 6
A 7
B 8
B 9
... ...

I want to create a clasification, I must take the three
best positions of each team:

Example A=1+3+5=9
B=2+8+9=19
C=...

I see noway to do that without PLSQL, and without temporary
tables.

Thanks in advance and sorry for my english.
Tarko

 

[connectxyzzy]

In the days before PL/SQL, we would code something like this (sometimes in a view....)



select sum(decode,team,'A',Position,0) Ateam,
sum(decode,team,'B',Position,0) Bteam,
sum(decode,team,'C',Position,0) Cteam From resultstab
group by team,position
having count(*) < 4
order by team,position

Haven't tried it, but it should be close. Might bog down the DB if it's a huge table..

 

[mrcomino]

Nope, it seems not to work...
I need the adition of the three best values, and not
the sum() of the teams with 3 or less values...

 

[karluk]

If you know for sure that every team has at least three entrants, the following query should work.

SELECT A.TEAM, MIN(A.POSITION + B.POSITION + C.POSITION)
FROM TEAMS A, TEAMS B, TEAMS C
WHERE A.TEAM = B.TEAM
AND B.TEAM = C.TEAM
AND A.POSITION < B.POSITION
AND B.POSITION < C.POSITION
GROUP BY A.TEAM
ORDER BY 1

 

[mrcomino]

Cool karluk, that is more or less what i lookin for.
Thanks

I need to thinnk a little more to allow shared positions
Example: two members of team A can arrive at the same time and be sharing the position 5.

Thanks again karluk

 

[mrcomino]

to allow sharing positions ...

SELECT A.TEAM, MIN(A.POSITION + B.POSITION + C.POSITION)
FROM TEAMS A, TEAMS B, TEAMS C
WHERE A.TEAM = B.TEAM
AND B.TEAM = C.TEAM
AND A.POSITION <= B.POSITION
AND B.POSITION <= C.POSITION
and A.rowid!=B.rowid
and B.rowid!=C.rowid
and A.rowid!=C.rowid
GROUP BY A.TEAM
ORDER BY 1 ;

 

[mrcomino]

to allow sharing positions ...

SELECT A.TEAM, MIN(A.POSITION + B.POSITION + C.POSITION)
FROM TEAMS A, TEAMS B, TEAMS C
WHERE A.TEAM = B.TEAM
AND B.TEAM = C.TEAM
AND A.POSITION <= B.POSITION
AND B.POSITION <= C.POSITION
and A.rowid!=B.rowid
and B.rowid!=C.rowid
and A.rowid!=C.rowid
GROUP BY A.TEAM
ORDER BY 1 ;