Hi,
I am quite late at getting back to this, but I thought I would go ahead and
post the test case. What I want is the "rank" of a user's score, which is
the row number of a user after sorting the table in descending order.
Here is a table with some data:
create table scores(
userId int,
score int
);
INSERT into scores VALUES (1, 1);
INSERT into scores VALUES (2, 100);
INSERT into scores VALUES (3, 25);
INSERT into scores VALUES (4, 75);
SELECT *, rownum as rn from (select userId, score from scores ORDER BY
score DESC);
After sorting, we see that userId=4 has a rank (rowNum) of 2. This works
fine. But lets say that we just want to obtain the rank of a particular
user (in this case 4). I try this sql command, which just wraps the result
in another sub query with a where clause:
SELECT * from (select *, rownum as rn from (select userId, score from
scores ORDER BY score DESC)) WHERE userId=4;
But here, we get the rowNum (as rn) of userId as 1. Perhaps I am missing
something here. Any ideas?
Thanks,
-Adam
On Sunday, March 17, 2013 2:25:46 PM UTC-4, Thomas Mueller wrote:
>
> Hi,
>
> Could you try to provide a complete and reproducible test case that is as
> simple as possible?
>
> Regards,
> Thomas
>
>
>
> On Wed, Feb 27, 2013 at 7:20 PM, Adam McMahon <ad...@cs.miami.edu<javascript:>
> > wrote:
>
>> Hi,
>>
>> I am trying to get the "rank" of a user's score in an H2 Database.
>>
>> The following query gives me all the scores ordered descending with a
>> corresponding row number (this part works):
>>
>> * *select *, rownum as rn from (select userId, score from nw_data
>> ORDER BY score DESC)
>>
>> But I only want to find the rank of an individual user (say userId=3), so
>> wrapped this in another select query:
>>
>> select * from (select *, rownum as rn from (select userId, score
>> from nw_data ORDER BY score DESC)) WHERE userId=3;
>>
>> I would have expected this to work, but "rn" (the output for rownum) is
>> always "1". It is as though the rownum function is being repeated in the
>> final outer select, when really I just want the value that was previously
>> computed. Clearly I am missing something here. Any ideas on how to
>> structure this?
>>
>> Thanks,
>>
>> -Adam
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to h2-database...@googlegroups.com <javascript:>.
>> To post to this group, send email to h2-da...@googlegroups.com<javascript:>
>> .
>> Visit this group at http://groups.google.com/group/h2-database?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
