Jump to content

How to return a row number?


bkpaul

Recommended Posts

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.