FIELDS COLUMNS ROWS ???

[pee2be]

I've got a Table called formula_play with four fileds gamer_id ,Time_first,Time_second and Time_third. The values are for example.

gamer_id Paul Johan
Time_first 80 120
Time_second 60 80
Time_third 70 40

Now what I'd like to do is to show the lowest value of the three fields wich have the data. So...

Paul 60
Johan 40

I've tried almost everything but no luck

Hope you guys can help me.

THX in advance.....

Paul

 

[r937]

if you can change your table structure around, this would be a lot easier

as it is, try a CASE structure

select player
, case
when time_first >= time_second
and time_second >= time_third
then time_third
when time_first >= time_third
and time_third >= time_second
then time_second
when time_second >= time_first
and time_first >= time_third
then time_third
when time_second >= time_third
and time_third >= time_first
then time_first
when time_third >= time_first
and time_first >= time_second
then time_second
when time_third >= time_second
and time_second >= time_first
then time_first
else 0
end as playerlow
from yourtable

rudy

http://rudy.ca/

 

[pee2be]

rudy

THX for your quick response, but how would you prefere the structure of the database?

Paul

 

[r937]

[tt]create table players
( id smallint not null auto_increment
, name varchar(50) not null
)
create table playertimes
( playerid smallint not null
, instance smallint not null
, playertime smallint not null
)[/tt]

sample data --

players
1 Paul
2 Johan

playertimes
1 1 80
1 2 60
1 3 70
2 1 120
2 2 80
2 3 40

get players' lowest time --

[tt]select name
, min(playertime) as playerlow
from players
inner
join playertimes
on players.id = playertimes.playerid
group
by name[/tt]



rudy

 

[pee2be]

Hi rudy,

Once again thx for the quick responses.

Well I created the new tables, but inserting your data it gives *no data*. WHile I already inserted data.

id name
1 paul
2 jeroen
3 peter
4 arthur
5 theo
6 werwer

playerid instance playertime
0 60 80
0 70 30

I'll guess that Playerid 0 should be paul or Jeroen. ANd this is based on two times.....Is it also doable with 3 times?....

THX Paul@gamesyndicate.nl

 

[r937]

it is doable with any number of instances, that's the beauty of it -- just imagine how that CASE structure will explode if there were four instances instead of three...

as for your data, just look at the tables again carefully, the multiple instances for each player don't go across a row, they go down a column

here's the data for paul --

playertimes
1 1 80
1 2 60
1 3 70

the first column is the playerid, in this case paul

the second column are his three instances

the third column are his times for each of those instances

if you wanted to record a fourth time for paul, it would be

1 4 120


rudy

 

[pee2be]

Dear Rudy,

OK I'm stuck again. I've got now as follows:
Player with PlayerID PlayerName
and Game with GameID PlayerID GameName Timefirst Timesecond and Timethird.

But every time I try to use MAX() or MIN() it still gives mee the MAX() of the whole colomn instead of the cell of that particular PlayerID.

Could you pls help me once again.

THX in advance

 

[r937]

if you have Timefirst Timesecond and Timethird, you'll need the CASE structure

try thge structure i suggested

create table playertimes
( playerid smallint not null
, instance smallint not null
, playertime smallint not null
)


rudy