How to return a row number?

[bkpaul]

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.

[fenway]

Row number? Why? Just count them yourself.

[otuatail]

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'

[bkpaul]

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?

[bkpaul]

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.

[fenway]

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.

[bkpaul]

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.

[fenway]

I don't see why you need the row number at all.

[bkpaul]

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.

[fenway]

How will it help you?

[btherl]

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"

[fenway]

Yes, ranks are complicated... there are a variety of ways to do this, all of which are uniquely complex.

[bkpaul]

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!

[btherl]

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.

[fenway]

Take a look here.