This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes SQL Question: Getting the rank Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Question: Getting the rank" Watch "SQL Question: Getting the rank" New topic
Author

SQL Question: Getting the rank

Svend Rost
Ranch Hand

Joined: Oct 23, 2002
Posts: 904
Hi,

I've got a table called Persons, who contain persons. Each person has a
value and I wish to get a "persons rank".

I need to print the result in a browser btw. (not that it matters..)

I tried the following:



my idea was, that I would get the sorted posts, and then simplt iterate
through the posts. Ofcause, I could return the sorted result set, and
then iterate through the set myself, but I guess it would be slower/
more resource demanding.

Anyone got any pointers?

Thanks in advance,

/Svend Rost
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Can you explain your table a bit more? Your SQL seem to be assuming multiple rows for single users, rather then a single row. Which is it?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Svend Rost
Ranch Hand

Joined: Oct 23, 2002
Posts: 904
Thanks for the reply.

My tubles look like this: Person(id, name, value).

The table isn't sorted, and after I update a persons value I'd
like to get his/her rank.



/Svend Rost
Svend Rost
Ranch Hand

Joined: Oct 23, 2002
Posts: 904
hmm..


the above seems to work - but it doesn't return what I need. It returns 1
(I guess that's because there's only one tuble with id=2).

Any got a pointer?

Edit: Now that I think about it.. how about JOIN.. would it not be possible
to do something like select count(*) from player as p1, player as p2 where ?
Edit 2: Im trying something like:
SELECT COUNT(*) FROM player p1, player p2 WHERE p1.id=10 AND p1.score>p2.score;
at the moment, but I get a "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." error when I try to
run the query.

[ May 09, 2005: Message edited by: Svend Rost ]
[ May 09, 2005: Message edited by: Svend Rost ]
Svend Rost
Ranch Hand

Joined: Oct 23, 2002
Posts: 904
Problem solved.

I realised, that when I wanted to update a score, I (ofcause!) had to
transfer the value.. LOL

anyways, the final SLQ query was:

SELECT COUNT(*) FROM player WHERE "+Request.Querystring("score")+ "<player.score;

Please note, that you have to add 1 to the result you get from the above
query, to get the correct rank.

/Svend Rost
 
jQuery in Action, 2nd edition
 
subject: SQL Question: Getting the rank
 
Similar Threads
sorting an array from csv file.
read text and stroe date to the array
The result set behaviour
Returning top 10
I can't come up with a good design for...