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 <[email protected]<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 [email protected] <javascript:>.
>> To post to this group, send email to [email protected]<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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
