MX Dynamic Table Sorter Forum :: Numbering (ranking) a sortable table

I realize this falls outside the realm of Interakt support, but perhaps someone will have a quick solution.

I'm using MX Dynamic Table Sorter with Dreamweaver-Coldfusion-MYSQL.

I have a sortable dynamic table based on a recordset, which is working great! The recordset is a series of votes that fit a certain criteria (poll_id). Below is a simplified example of the query:

SELECT vote_choice, poll_id, COUNT(*) AS numberof

FROM table

WHERE poll_id=#URL.id#

GROUP BY vote_choice

ORDER BY (MX sortable dynamic table URL variable)

What I want is an incrementing numeric column (1,2,3...) in the dynamic table that will act as the rank of the results. In other words, the vote_choice with the highest count will have rank 1, second place will have rank 2, etc.

#CurrentRow# function will not work because it does not get tied to a specific record when the table is resorted by the user. Any thoughts?

Thanks!

Hello Eric,

What if you simply add before your repeat region manually a variable which is equal with the recordset page number * number of records per page. Inside the repeat region you should just increase this variable with 1 and then to display it. Now you have a record number.

Regards,
Cristian MARIN

Hello Eric,

I has in hurry and I didn't read until the last phrase of your post. Unfortunatelly for your problem there is no "one step" solution. This because the MySQL as far as I know has no dedicated function to extract the rank of a number from a list of numbers. 

This leave you with 2 options:

1. You'll have to extract all the information from the table and compute the number ranking manually.

2. You keep the ranking into a separate field of your table and you'll have it on each display. However for each insert or update you'll have to find the record ranking and update the others as well. 

I would go for the second solution as the server resources required will increase in usage only on Insert/Update instead of doing the entire table re-ordering at each page view.

Regards,
Cristian MARIN