Hello, you,

TableJX seems to be a good solution for many purposes.

I have a challenge whant to be resolved.

I have a MS SQL table with the following fields:

Tour ID - Player - Match number - points
1 1 1 5
1 2 1 6
1 3 1 6
1 4 1 5
1 1 2 6
1 2 2 5
1 3 2 6
1 4 2 9


From this table we I have the following data shown:

Rank - Player - Match number 1 - Match number 2 - Total
1 4 5 9 14
2 3 6 5 11
2 2 5 6 11
3 1 6 3 9


Hope you can help me and I buy your solution at the same if i could show how.

Looking forward to hear form you.

Hi,

CREATE VIEW Ranking as
Select (row_number() over (order by total desc)) as rank, *
from
(Select M1.S, M1.M1, M2.M2, (M1.M1 + M2.M2) as total
from (SELECT T1.IdPlayer AS S, SUM(T1.Points) AS M1
FROM dbo.Tour T1
WHERE T1.MatchNumber = 1
GROUP BY T1.IdPlayer
)AS M1,
(SELECT T1.IdPlayer AS S, SUM(T1.Points) AS M2
FROM dbo.Tour T1
WHERE T1.MatchNumber = 2
GROUP BY T1.IdPlayer
)AS M2
WHERE M1.S = M2.S) tab


Show view Ranking in TableJX.

Regards,
Viljem

Thanks Viljem for you quick reply

Just one qustions so I understand it rigth.

"Select M1.S" what is the "S" I presume that M1 is matchnumber?

And do i need to create tow new fields in the table - Rank and Total ?

Last but not least will this code work if there are many players and matches. Asked in another way, are this dynamic, sorry I mised that i my first reply.


Cheers

Hi,

this SQL code will work on first table - Tour and will produce SQL view as output as you described in second table - Ranking.

This works if you have 2 matches, 1 and 2. If you have more matches, then you have to consider to change data model with new table, let say Matches:

IdMatch Points
1 100
2 110
...

If you connect table Player with Matches (one-to-many), you will have IdPlayer as foreign key in table Matches:

IdMatch IdPlayer Points
1 1 100
2 1 110
3 2 50
...

Then SQL will be different. You can have many data models. It depends on what queries do you need from DB.

About SQL view:
1. no new rows are needed because this is a sql view
2. M1 is subselect (or subtable). M1.S is PlayerID in that subselect.

Regards,
Viljem

Thanks agian for you quick reply.

I will look at it, how i can be done , any way will i later this week ordre the TablejX for other issues.

thanks agian.