This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases 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 Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Question: Getting the rank" Watch "SQL Question: Getting the rank" New topic

SQL Question: Getting the rank

Svend Rost
Ranch Hand

Joined: Oct 23, 2002
Posts: 904

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

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

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 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
I agree. Here's the link:
subject: SQL Question: Getting the rank
It's not a secret anymore!