heres the question

lets say you have a whole bunch of rows of data, the first colunm being the index (1,2,3 ect) and the next colunm being some numbers

is there a way to sort by the second colunm, and then say, how far down is index 2 from the top?

here an example


1 - 543
2 - 324
3 - 432
4 - 123
5 - 690

now we sort by column 2

5 - 690
1 - 543
3 - 432
2 - 324
4 - 123

is there a way to find the new position (example: index 2 is now #4)

If there's a way, I don't yet know of it, but I'm sure it could be done

Assuming that the second column in your database is called numbers you could do the following with your query:

Code:


Note that DESC can be replaced by ASC if you want ascending order instead. As for the second part of your question I am not quite sure how to do that aside from doing a second query and comparing the values.

I know how to do that part.the part that is complicated is the second part.

i was thinking that you could maybe do it, if you had a column that did not change when you sort it, which i dont know how you would do it, but it would be like this

1- 1 - 543
2 - 2 - 324
3 - 3 - 432
4 - 4 - 123
5 - 5 - 690

then sort

1 - 5 - 690
2 - 1 - 543
3 - 3 - 432
4 - 2 - 324
5 - 4 - 123


then you would just search for say "5" and find that the first column is "1" so it is the first result. now, how to do this is the tricky part

The naive method would be to select all rows with that sorting, and scan through the results until you found the row in question. That's very inefficient for anything production, though.

I was think that, but just like you, it seems really inefficient. if no one can think of another way, i can just do that, but maybe make it a cron that runs every hour or so and caches the results instead of having it run on every page load

Can you be more specific about what exactly this is for? I feel like it might be trivial to rephrase it as an easier-to-solve problem.

its for dynamic signatures for a website. it would say something like rhombus p. you are ranked 1st in post count and second in word count or something like that

If you're using MySQL, here's a solution if you want to do it entirely in SQL.

Firstly, you need the data. If I assume this is a table called members with a field called username and post_count:

Code:

We also need to assign each row with a rank. One way to do this would be to use a variable named rank and increment this for each returned result:

Code:

You would need to reset rank before running the query. You could do this with a separate SET @rank=0; query, or use a subquery to do this inline:

Code:

You now have a list of results in order of post count with a rank. We can now query those results with another query against the original table to only return the user we're interested in:

Code:


It's not pretty, but it "works" (you may find it faster to do the counting in PHP).

Edit: Of course, the above returns the results in the reverse order. Insert a DESC where appropriate.

Nice, Benryves! I didn't know that myself; that's something nifty to keep in mind.

wow thanks!

I hope you'll be sharing the signature project with us in the Your Projects subforum when you're ready; it sounds like a cool project.

As Kerm said, I hope we end up hearing about your project

Also, benryves made a very good last comment there, though. "You may find it faster to do the counting in PHP." It is much better to do this type of processing in the script as opposed to the MySQL server (assuming you're using MySQL). Too complex of a query can cause unwanted load on the MySQL server in a place that will increase execution times and can be seen as inefficient. In most cases it's much faster (and easier) to do this type of processing in PHP and not MySQL. PHP can handle a lot of this, and for the most part you just want your database to delivery its contents to your script as fast as possible so your script can continue to execute. Depending on two servers to communicate can cause a bottleneck, so you want to allow PHP to do what it can. If PHP can handle it without the help of MySQL, I would say use PHP. I normally only use very basic queries for the very reason I stated: All I want MySQL to do is give me my data! After that, PHP can focus on itself and really do what I want it to in the time I want it to.

This is also important to note if you were to write something complex that supported different types of databases, because you wouldn't want to have to write a complex query for every database type. In this case, PHP can be considered a life-saver, as it becomes painlessly "cross-DB-compatible" if you will, having supported all the major complex changes to the data script-side (as opposed to DB-side).

ffr. Of course, this is also me being a little more anal then most, and I also aim for highly optimized code that can be put to uses on much larger sites. I'm a bit excessive at times, and I apologize

I'll do the counterpoint for that. Having taken a database design class as part of my Masters' coursework, there's a lot of stuff that MySQL can do to optimize its queries based on the criteria and constraints in the user's query to minimize its hits on the database. Indeed, you can learn tricks with inner as opposed to outer as opposed to left or right joins in order to select your data quickly and efficiently, thus saving memory and cpu load of transferring out huge chunks of your database to local memory, then waiting while PHP parses and sorts it. In a large website, in fact, when the MySQL (database) and PHP (web) servers are on physically distinct machines, transferring large chunks of data from the database to webserver for post-processing would be a horrible idea, and learning to construct a query that could deliver a minimal dataset to the web server would be vital.

TL;DR I completely disagree and think your method will cause slowness and inefficiency.

so if they are both the same machine (which i think they are) what would you recommend?

also, kerm what you have on the profile page that looks something like this

is exactly what i need

That's a giant modification on phpBB built with my whole Archives system.

yes, but thats what im wanting to do, something like that the shows the rank, just like that page

The rank in what? You have your own archives system on your site, or you want to pull either Cemetech or ticalc.org ranks?