josborne

Well, I finally had the chance to get back to this. I really appreciate all the help and patience I have received but I am stuck again. I can easily create the cross-tab query if I know the number of Lap columns I will need: SELECT Rider_ID, SUM(IF(Lap=1, Lap_Total,0)) AS 'Lap 1', SUM(IF(Lap=2, Lap_Total,0)) AS 'Lap 2', SUM(IF(Lap=3, Lap_Total,0)) AS 'Lap 3', SUM(IF(Lap=4, Lap_Total,0)) AS 'Lap 4', SUM(IF(Lap=5, Lap_Total,0)) AS 'Lap 5', SUM(IF(Lap=6, Lap_Total,0)) AS 'Lap 6' FROM Lap_tbl WHERE Race_ID=37 GROUP BY Rider_ID In this case, I just had 6 laps However, I am not sure how to create some sort of loop to determine the number of distinct Lap value. I came across a way to do it using a stored procedure to generate the SQL statement. Unfortunately, my current web host doesn't allow creation of stored procedures.

The reason it is doing that is because the query is ranking your user ID after it is pulling the results. Essentially, it is seaching for your user ID then once it has that, it is applying the ranking. In order to get an individual ranking you need to pull all users, rank them then pull just your user ID. For that you would need to do this: SET @num :=0; SELECT a.username, a.user, a.Rank FROM ( SELECT b.username, a.user, SUM( a.exp_attack ) AS Attack, @num := @num +1 AS Rank FROM `rscd_experience` a JOIN `rscd_players` b ON `b`.`user` = a.`user` GROUP BY a.user ORDER BY Attack DESC ) AS a WHERE a.username = 'Kryptix' I think that will work. I don't have the rscd_players table to test it.

Eep! You are right. I was copying the query from one I use that has to sum several values and i forgot to remove it. The corrected query should be: SET @num :=0; SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank FROM `rscd_experience` a ORDER BY a.exp_attack DESC

When it displays the results, it only shows the last query at the top. Did you try the query fenway posted?: SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank FROM `rscd_experience` a, ( SELECT @num := 0 ) b GROUP BY a.user ORDER BY a.exp_attack DESC As I mentioned, this query gives me the weird results that my query is giving you but maybe it will work for you.

The JOIN should work like any other JOIN. Play with it, you'll figure it out. The JOIN will come after the SELECT. You might also want to pick up a good MySQL or database book. In a day, you'd have at least an understanding of how to build, normalize and index the database. Believe me, knowing that stuff will save you a ton of effort later on.

I just ran this query: SET @num :=0; SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank FROM `rscd_experience` a GROUP BY a.user ORDER BY a.exp_attack DESC exactly like that through PHPMyadmin and it work perfectly. There is something I am not getting here. user Attack Rank 71684325834627 76239612 1 148139693 59175864 2 983153 57564069 3 38261171 55051182 4 3203405653017260 53017189 5 7897310397846 45193294 6 71711257452993 41300376 7 77130664793626944 40323414 8 8482433060366 39848166 9 49577460057 36624060 10 4672448 35501760 11 21167447082 35184450 12 1779170004809624 34269840 13 8804383563837 34100295 14 1604541127 33556662 15 19446421692267282 32674434 16 288424075748 32317470 17 2664494867034954 31643868 18 34822228156 31156614 19 21602605516 30225240 20 1660564469941630521 30120840 21 4369001 29810814 22 595099269 28966866 23 683134 28410894 24 269892 28185714 25 1722929401841 28168098 26 1260834943459 27503256 27 16883898 27490044 28 721552 27422532 29 695883957 26070606 30

Hmmm...that query works perfectly for me. Are you sure you are copy/pasting the whole thing correctly? You should be able to paste the whole thing and run both queries in PHP admin. Weird. I just tried fenway's query and got exactly the same result you posted that I believe is when you ran the set variable and query separately. fenway, you are the master and I am out of my league. There appears to be something going on with the variable and I can't figure it out.

OKay, Try fenway's query. If that doesn't work export the table a SQL (through the "Export" tab) copy the results to notepad and save it. Then upload it here as an attachment. This way I can run the query myself.

Not sure about the error message (though setting the variable in the statement itself will be very useful) but that query will pull all the records, sort them by exp_attack from highest to lowest and number each result. That number is the ranking.

Okay, let's get the ranking working before worrying about the join. I made a mistake and made things a little more complicated than needed. SET @num :=0; SELECT a.user, a.exp_attack AS Attack, @num := @num +1 AS Rank FROM `rscd_experience` a GROUP BY a.user ORDER BY a.exp_attack DESC Please note that you must include the SET @num :=0; bit at the top. If you don't it ranking will be out of order.

Your tables are `rscd_experience` and `rscd_players`, right? If so, run this command: SHOW CREATE TABLE `rscd_experience` then post the results. Then post this command: SHOW CREATE TABLE `rscd_players` and post the results inside. It will be a lot easier to figure out why that isn't working and to create the join witht hat info.

Well, it is a little hard to know if this is right because I am not totally clear on the table structures. Using SHOW CREATE TABLE is typically the best way to get help around here. Anyway, from what I can tell, this would give you the attack_exp ranking for all users: SET @num :=0; SELECT a.user, SUM( a.exp_attack ) AS Attack, @num := @num +1 AS Rank FROM `rscd_experience` a GROUP BY a.user ORDER BY Attack DESC While this will give you the raking for an individual user (based on user ID): SET @num :=0; SELECT user, a.Rank FROM ( SELECT a.user, SUM( a.exp_attack ) AS Attack, @num := @num +1 AS Rank FROM `rscd_experience` a GROUP BY a.user ORDER BY Attack DESC ) AS a WHERE a.user = 1 Hope that helps.

This is actually a problem I ran into when starting out. There is a pretty easy solution but it will be a lot easier to make sense out of it if you provided the table structure(s) and maybe a sample query you'd like to run.

Ahh! Okay. Having a name for it helps a lot. I have found some good information. I have realized that the queries I posted above won't work because of the SUM. I am trying to build a query that will. I'm not quite sure how to use the max # of loops though. Any help would be greatly appreciated.

I guess there is no "edit post" function. Anyway, another option would be SELECT Rider_ID, if(Lap=1,Lap_Total,null) as Lap1, if(Lap=2,Lap_Total,null) as Lap2, if(Lap=3,Lap_Total,null) as Lap3, if(Lap=4,Lap_Total,null) as Lap4 from Lap_tbl; But this, like the join would require that I know the number of Lap values so if results set contained 16 Lap values, the query would look like this: SELECT Rider_ID, if(Lap=1,Lap_Total,null) as Lap1, if(Lap=2,Lap_Total,null) as Lap2, if(Lap=3,Lap_Total,null) as Lap3, if(Lap=4,Lap_Total,null) as Lap4, if(Lap=5,Lap_Total,null) as Lap5, if(Lap=6,Lap_Total,null) as Lap6, if(Lap=7,Lap_Total,null) as Lap7, if(Lap=8,Lap_Total,null) as Lap8, if(Lap=9,Lap_Total,null) as Lap9, if(Lap=10,Lap_Total,null) as Lap10, if(Lap=11,Lap_Total,null) as Lap11, if(Lap=12,Lap_Total,null) as Lap12, if(Lap=13,Lap_Total,null) as Lap13, if(Lap=14,Lap_Total,null) as Lap14, if(Lap=15,Lap_Total,null) as Lap15, if(Lap=16,Lap_Total,null) as Lap16 from Lap_tbl; But if there were 20 Lap values, I wouldn't get the last 4. Theoretically, I could just run that query out to the maximum number of Lap values possible (about 35) and if the Lap values only went to 20, the query would simply return NULL values for the last 15. Not very elegant.