bkpaul Posted October 11, 2007 Share Posted October 11, 2007 Hi Folks, Newbie here again Using MySQL 5.0 CF8 DW CS3 I guess as usual this is a really simple question requiring a really simple answer but my head has a black hole at the moment so if someone could help me out I would be greatful I have a query: <cfquery name="rstrdt" datasource="cdsl"> SELECT * FROM players ORDER BY winpercentage DESC,played DESC,banktotal DESC </cfquery> the table the query creates has many colums but 2 of the columns are 'player' & 'club thus my question is: how would I return the row number where the player and his club are in the table, the primary key 'playerindex' identifies the player uniquely so that is of no use to me in this instance. thanks for your time. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 11, 2007 Share Posted October 11, 2007 Row number? Why? Just count them yourself. Quote Link to comment Share on other sites More sharing options...
otuatail Posted October 12, 2007 Share Posted October 12, 2007 SELECT * FROM players where player = 'Fred' and club = 'Bolton' ORDER BY winpercentage DESC,played DESC,banktotal DESC use mysql_num_rows() AND primary key 'playerindex' Quote Link to comment Share on other sites More sharing options...
bkpaul Posted October 12, 2007 Author Share Posted October 12, 2007 Cheers, otuatail but that doesn't help, maybe I should explain the table is sorted then AFTER the sort I need to find out on what row in the table a particular player is as said the primary key doens't help in this instance Fenway, I don't follow your answer m8y? Quote Link to comment Share on other sites More sharing options...
bkpaul Posted October 12, 2007 Author Share Posted October 12, 2007 Although I didn't follow your answer fenway you did give me an idea solution to the problem was <cfoutput query="rstrdt" > <cfif #rstrdt.playerfullname# IS #url.player# AND #rstrdt.club# IS #url.team#> <cfset position=#rstrdt.currentrow#> </cfif> </cfoutput> If anyone can help with a less wasteful way of doing this it would be appreciated afterall searching through potentially thousands of records in this manner just doesn't seem to me to be the best use of resources and I am pretty certain there will be a better way. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 12, 2007 Share Posted October 12, 2007 Although I didn't follow your answer fenway you did give me an idea solution to the problem was <cfoutput query="rstrdt" > <cfif #rstrdt.playerfullname# IS #url.player# AND #rstrdt.club# IS #url.team#> <cfset position=#rstrdt.currentrow#> </cfif> </cfoutput> If anyone can help with a less wasteful way of doing this it would be appreciated afterall searching through potentially thousands of records in this manner just doesn't seem to me to be the best use of resources and I am pretty certain there will be a better way. I don't know anything about CF... if you want to get the "row number", you have to manually go through all of the rows you get back, and keep your own counter... I guess if it was reasonably long, you could pre-load a list of "sorted IDs", and then find the number yourself. Quote Link to comment Share on other sites More sharing options...
bkpaul Posted October 13, 2007 Author Share Posted October 13, 2007 I was thinking along the lines of creating a temporary table with its primary key equal in records to my table and doing some sort of join to ascertain the actual row I wanted.... I am worse than rubbish at joins so I don't know if that is possible or not...just an idea. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 15, 2007 Share Posted October 15, 2007 I don't see why you need the row number at all. Quote Link to comment Share on other sites More sharing options...
bkpaul Posted October 21, 2007 Author Share Posted October 21, 2007 I don't see why you need the row number at all. Whats your line of thinking behind that Fenway? do you know a better way? if so your advice would be most welcome. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 22, 2007 Share Posted October 22, 2007 How will it help you? Quote Link to comment Share on other sites More sharing options...
btherl Posted October 22, 2007 Share Posted October 22, 2007 I think the value he wants is the player's rank according to a specified sort order. The player is specified, and the output he wants is rank according to "winpercentage DESC,played DESC,banktotal DESC" Quote Link to comment Share on other sites More sharing options...
fenway Posted October 22, 2007 Share Posted October 22, 2007 Yes, ranks are complicated... there are a variety of ways to do this, all of which are uniquely complex. Quote Link to comment Share on other sites More sharing options...
bkpaul Posted October 23, 2007 Author Share Posted October 23, 2007 How will it help you? If its less resource hungry than my present solution it would be of significant benefit, it would also be an education for future projects. I just don't sit easy with the idea that you have to loop through each line until you get the line that meets a given criteria as whilst this may be okeyish for small amounts of data it would be stupid for tens of or hundereds of thousands of records. I know computers are quick and we are talking milliseconds (at best) and tenths at worst but many programmers are lazy these days in respect to resources they consume in their programs and to be honest I don't want to be. Premise: If a job is worth doing its worth doing well! Quote Link to comment Share on other sites More sharing options...
btherl Posted October 24, 2007 Share Posted October 24, 2007 If you query ranks a lot more often than you change them, then it may be worthwhile re-calculating all the ranks every time they are changed. You can store the ranks in a second table and join them. To improve on update times, you could use a bubble sort style method to bubble any altered entries into their correct position. For example: A 4 B 3.5 C 3 D 3 E 2.5 F 2 Let's say E gains 1 point. Then we bubble it up one step A 4 B 3.5 C 3 E 3.5 D 3 F 2 But it's still not in the correct position, so we bubble it up again A 4 B 3.5 E 3.5 C 3 D 3 F 2 Ok, now it's in the right order. This would only involve changing the auxiliary table which holds the ranks. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 24, 2007 Share Posted October 24, 2007 Take a look here. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.