[SOLVED] Complex Query - need help!

[jollygood]

Hey,

 

Having 3 tables. I want a end result that will give me 3 columns: Name(from the challenges table),Nickname(from the players table),Score(from the records table).

 

The table structure are:

Challenges

"Id","mediumint(9)","NO","PRI","","auto_increment"

"Uid","varchar(27)","NO","UNI"

"Name","varchar(50)","NO"

"Author","varchar(30)","NO"

"Environment","varchar(15)","NO"

 

Players

"Id","mediumint(9)","NO","PRI","","auto_increment"

"Login","varchar(50)","NO","UNI"

"Game","varchar(3)","NO","MUL"

"NickName","varchar(50)","NO"

"Nation","varchar(3)","NO"

"UpdatedAt","datetime","NO","","0000-00-00 00:00:00"

"Wins","mediumint(9)","NO"

"TimePlayed","mediumint(9)","NO"

"TeamName","char(60)","YES"

 

Records

"Id","mediumint(9)","NO","PRI","","auto_increment"

"ChallengeId","mediumint(9)","NO","MUL"

"PlayerId","mediumint(9)","NO"

"Score","mediumint(9)","NO"

"Date","datetime","NO","","0000-00-00 00:00:00"

 

The join is records.challengeid<->challenges.id....records.playerid<->player.id.

 

There are currently 177 different challenges. Each challenge could have from 0 to many records done by different players. The latest records (date) is the one that will count. Since the score is acctualy the time it took to complete the challenge there could be several players with the exact same time. But I'm only interrested in the one player that has the best time with the latest date.

 

Putting a test below so you get the idea:

"RecordID","Name","NickName","Score","PlayerID","date"

16888,"$W$i$000Black $00fSmooht","asd",44020,5387,"2008-05-15 01:33:10"

18824,"$W$i$000Black $00fSmooht","Grossi",44020,3406,"2008-05-18 19:45:01"

26202,"$W$i$000Black $00fSmooht","adf",44050,9506,"2008-06-04 09:38:08"

4942,"$W$i$000Black $00fSmooht","vxcv",44150,22,"2008-04-27 13:38:49"

17456,"$W$i$000Black $00fSmooht","borta",44180,152,"2008-05-16 18:49:51"

27720,"$W$i$000Black $00fSmooht","Markus",44190,366,"2008-06-07 12:12:42"

25160,"$W$i$000Black $00fSmooht","v<v",44320,3111,"2008-05-30 17:04:48"

28362,"$W$i$000Black $00fSmooht","rgr",44320,526,"2008-06-08 17:44:45"

8769,"$W$i$000Black $00fSmooht","trtg",44410,15,"2008-05-01 18:14:32"

24312,"$W$i$000Black $00fSmooht","mnbcm",44450,7764,"2008-05-27 16:13:35"

31315,"$W$i$000Black $00fSmooht","flag4711",44850,2904,"2008-06-19 23:00:46"

11625,"$W$i$000Black $00fSmooht","kjc",47900,1,"2008-05-06 11:27:31"

16439,"$W$i$000Black $00fSmooht","bibeto",53260,6959,"2008-05-14 15:45:04"

7587,"$W$i$000Black $00fSmooht","tert",61910,2412,"2008-04-28 20:36:40"

15072,"$W$i$000Black $00fSmooht"," herg",70880,4588,"2008-05-10 22:55:48"

24081,"$W$i$000Black $00fSmooht","Holy Cow!",87830,7081,"2008-05-26 19:52:18"

20634,"$W$i$000Black $00fSmooht","Macy",93650,137,"2008-05-21 10:47:29"

29661,"$W$i$000Black $00fSmooht","iumm",94270,1129,"2008-06-12 23:50:13"

26618,"$W$i$000Black $00fSmooht","rwer",96610,6691,"2008-06-04 20:12:31"

24080,"$W$i$000Black $00fSmooht","mackrejv",113510,3418,"2008-05-26 19:51:43"

16440,"$W$i$000Black $00fSmooht","peace_train",129050,154,"2008-05-14 15:46:39"

24082,"$W$i$000Black $00fSmooht","vbmnso",148780,5921,"2008-05-26 19:52:30"

31316,"$W$i$000Black $00fSmooht","minikarlsson",154000,5578,"2008-06-19 23:01:16"

 

How would I construct the SQL query to get my 3 column result?

 

Sorry, forgot to include server version....it's MYSQL 5.0.45.

[fenway]

Isn't this a simple ORDER BY score DESC LIMIT 1 on the records table (and then joins to get the details)?

[jollygood]

Isn't this a simple ORDER BY score DESC LIMIT 1 on the records table (and then joins to get the details)?

 

No, it's not. If you take a look at my exctracted example you notice that for this specific challenge there are currently 2 players holding the same score but it's accualy the second player "Grossi" that have the number one position since he is the one with the latest date and time for that score.

 

Regardless I really have no clue on how I should construct the SQL so any help on that would be appreciated

[fenway]

So then order by score DESC, `date` DESC LIMIT 1.

 

BTW, it's bad to use a reserved keyword as the name of your field.

[jollygood]

Have got this so far...but I'm stuck at this point. Don't know how to continue to get it to show 1 row for each of the 177 different challanges. This one only gives me 1 result since I have the where clause. If I skip the where clause I'm guessing it gives me the one single row that are the lowest score with the latest date.

 

 

SELECT c.Name, p.NickName, r.Score, r.date

FROM challenges c

LEFT JOIN records r on (r.ChallengeId=c.Id)

LEFT JOIN players p on (r.PlayerId=p.Id)

WHERE c.id=13

ORDER BY r.Score ASC,r.Date DESC LIMIT 1;

 

"Name",                          "NickName","Score","date"

"$W$i$000Black $00fSmooht","Grossi",44020,"2008-05-18 19:45:01"

 

How should I proceed?

[fenway]

Don't know how to continue to get it to show 1 row for each of the 177 different challanges.

That's not what you described before.... so instead, you need to get the lowest score for each challenge first, and then join this result back selecting the most recent date.

[jollygood]

sorry if I was unclear.

 

How should I make the sql query?

[fenway]

select recordID, max(date) from records where row( challengeID, score ) = ( select challengeID, min(score) from records group by challengeID ) group by challengeID

 

[jollygood]

subquery returns more than one row. ErrorNr. 1242

[fenway]

Sorry, replace = with IN, my bad.

[jollygood]

not working. The query runs forever. I forced it to stop after 3 minutes.

[fenway]

Post the EXPLAIN... maybe you're missing indexes.

[jollygood]

 

id,"select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"

1,"PRIMARY","records","index","","ChallengeId","6","",33150,"Using where"

2,"DEPENDENT SUBQUERY","records","index","","ChallengeId","6","",33150,""

 

getting Error 1003 when doing the explain.

 

[fenway]

I don't see any proper keys there....

 

Oops, tired... try this instead:

 

select r.recordID, max(r.date)

from records AS r

inner join

( select challengeID, min(score) AS minScore from records group by challengeID ) as t2 on ( t2.challengeID = r.challengeID AND t2.minScore = r.score )

group by r. challengeID

[jollygood]

Ok, now I got a result, thanks 

 

Almost there then...now I only need to join all togehter with the players table to get the player Nickname and the challenges table to get the name of the Challenge.

 

Can I do that in one query with subquery or similar or should I create a view that helps?

 

Tried to create a view based on this query you just wrote but that did not work.

[fenway]

You'd have to join that query back to your records table, and then find the corresponding record, and then join that one to the other tables.

[jollygood]

and this was my initial question and also the title of this post. That it is a complex query and I need help to complete it....the total query that gives the final result of the 3 columns.

[fenway]

and this was my initial question and also the title of this post. That it is a complex query and I need help to complete it....the total query that gives the final result of the 3 columns.

I know what you need; I was just hoping to see some further attempts to "complete it"; you already have the complex part.

 

records.challengeid<->challenges.id....records.playerid<->player.id.

 

select r2.score, p.name, c.name from
( select r.recordID, max(r.date) AS maxDate
from records AS r
inner join
( select challengeID, min(score) AS minScore from records group by challengeID ) as t2 on ( t2.challengeID = r.challengeID AND t2.minScore = r.score )
group by r. challengeID ) as r1
inner join records as r2 on ( r2.recordID = r1.recordID and r2.date = r1.maxDate )
inner join players as p on ( p.id = r2.playerid )
inner join challenges as c on ( c.id = r2.challengeid )

[jollygood]

Thank you. I would never had figured it out. Don't know much about SQL...only the basics.

 

The final query that is working for me is:

 

select c.name AS ChallengeName, p.nickname, r2.score from

( select r.ID, max(r.date) AS maxDate

from records AS r

inner join

( select challengeID, min(score) AS minScore from records group by challengeID ) as t2 on ( t2.challengeID = r.challengeID AND t2.minScore = r.score )

group by r. challengeID ) as r1

inner join records as r2 on ( r2.ID = r1.ID and r2.date = r1.maxDate )

inner join players as p on ( p.id = r2.playerid )

inner join challenges as c on ( c.id = r2.challengeid )

 

Thanks again - issue solved!