4 replies to this topic

[BASHERS33]

What is the best way to get a ranking in mysql? Say I want to order rows by fieldA DESC and I want to know the rank where fieldB=5.

I am hoping there is a decent way to do it without changing how I currently have my database set up. But as I search online I only see complicated explanations on how to do this. I am using php, by the way, but I assume it would not be ideal to get every row and count++ until I get to the row I want. That would be bad for big tables.

Also people told me when using pagination it's best to use LIMIT in the query than to get all rows and then do an array_slice in php. But now I read that it's BAD to use offsets in limits! So I keep going to all this trouble to change my programming based on what people tell me and then I end up finding out they left out all of these drawbacks to various methods.

[WingedPanther]

You'll have to start by defining your ranking system.

[BASHERS33]

Define as in a file or define as in define to you? Basically it would be a ranking of an int column. It's currently showing the top 50 results and I thought it would be nice to put below it "your rank" and it have the member viewing's rank.

[Orjan]

just count how many rows are better ranked than the one you are looking for and add 1?

[BASHERS33]

Blah, I feel dumb because that is probably the easiest way. I should have thought of that. I hate to add an extra query, but a count query is most likely more efficient than another method.

I guess where it would be tricky is if I had a long list and wanted a ranking of each one without it actually ordered by it. But in my case I guess your idea is the easiest and best way.

I will make it where ONLY if the eprson is not in that top 50 will it then do the count query. Otherwise it won't even need it.