On Mon, 08 Feb 2010 16:27:20 +0100, Johannes Keßler wrote:
>
> On 08/02/10 15:56, Peter H. Coffin wrote:
>> (Hmm... Now that I'm thinking about rankings tangentially, one of the
>> questions is going to have to eventually be addressed in the problem
>> someplace is if you're ranking by val1, out of the table ordered by val1
>> looking like
>>
>> name val1
>> --------------- --------
>> Bob 3
>> Trevor 3
>> Mary 7
>> Harry 9
>> Jane 9
>> Marvin 9
>> Norman 10
>> Paul 32
>> Geoff 103
>>
>> "What position does Marvin occupy?" and "Who's in the top five?" might
>> become serious questions with non-obvious answers... )
>>
>
> Thanks for clearing this up.
>
> I first failed to explain but this is exactly the case I have.

What? Like that sample table? You can join the table to itself with an
inequality and do something like

select count(*)
from my_table a join my_table b on a.val1 >= b.val1
where b.name = 'Marvin' ;

which will retufn 6, meaning that Marvin is no lower than the sixth
place. If you want to be generous and include people at the higher end
of the scale, saying that the group with val1 = 9 are tied for 4th,
you use a > connector in the join instead of >= and add 1 to the count.

For a "top 5" scenario, you first have to decide whether you want to
include all the equal items and potentially end up with more than five
items, or exclude equals and potentially end up with fewer. I find it
easier to think about as a subquery:

select a.name
from count_test a
where a.val1 >= (select min(b.val1)
from (select c.val1
from count_test c
order by val1 desc
limit 5) b
)
;

This one gets the "potentially more items than strict limit" version.
Changing >= to > will result in the "potentially fewer items" version.
Which to choose will probably depend on how expensive your prizes are.

As long as you have indexes properly built, such as making sure to have
one on val1 so that the innermost subquery runs quickly, it shouldn't
take long to run any of these on even a very large table. The optimizer
will, in the second case, very quickly build its small derived table, so
the b query runs quickly (there's only 5 rows to find the min() of), and
that makes the a query part very simple as well.

--
16 megs in a '95 box! Yo Ho Ho and a battle of RAM!

Banana wrote:
> Jerry Stuckle wrote:
>> Doug, please see my earlier post. Rankings such as this are quite
>> common in many different areas.
>>
>> Rather than tell him why he doesn't need the information, maybe you
>> could help him with an answer. I'm trying to figure out how to do it
>> without updating a table each time. It's an interesting problem...
>
> I already posted a example of generating a position listing using local
> variables which I believed that was what OP was after, and not ranking
> (e.g. handles the ties in some manner). But I imagine you can adapt the
> idea to use an expression to test if the rank value should be
> incremented or not based on whether there is a tie, but that would mean
> needing two local variables, a incrementing value and a 'flag' that a
> tie was found to enable skipping incrementing and then incrementing by
> 2, all wrapped in a IF().
>
> That would be considerably faster than doing a correlated subqueries, a
> traditional SQL solution for ranking, I would think.

Not necessarily. Your solution requires retrieving multiple rows of
data which are just thrown away. This probably would be faster if the
user is near the top of the list, but could be slower if the user is far
down the rankings. A subquery would only retrieve the requested row,
but would have to number all rows.

And your code is similar to the idea I have. I, like you, am assuming
there would be no ties - probably not a valid assumption, but it makes
coding easier, at least to start.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Johannes Keßler wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 08/02/10 16:03, Jerry Stuckle wrote:
>> Doug Miller wrote:
>>> In article <hkp2tr$rc1$03$2@news.t-online.com>,
>>> =?UTF-8?B?Sm9oYW5uZXMgS2XDn2xlcg==?= <mail@bananas-playground.net> wrote:
>>>> -----BEGIN PGP SIGNED MESSAGE-----
>>>> Hash: SHA1
>>>>
>>>> On 08/02/10 13:51, Doug Miller wrote:
>>>>> In article <hkov40$tnc$00$1@news.t-online.com>,
>>>> =?ISO-8859-1?Q?Johannes_Ke=DFler?= <mail@bananas-playground.net> wrote:
>>>>>> Sorry if this is not as clear as it should be.
>>>>>>
>>>>>> here is an example
>>>>>>
>>>>>> customerid|name|price|points|sell
>>>>>> - ---------------------------------
>>>>>> 1|name1|10|22|1
>>>>>> 2|name2|3|1|25
>>>>>> 3|name3|15|84|23
>>>>>> 4|name4|122|81|22
>>>>>>
>>>>>> SELECT * FROM table ORDER BY price DESC
>>>>>>
>>>>>> result would be:
>>>>>>
>>>>>> customerid|name|price|points|sell
>>>>>> - ---------------------------------
>>>>>> 4|name4|122|81|22
>>>>>> 3|name3|15|84|23
>>>>>> 1|name1|10|22|1
>>>>>> 2|name2|3|1|25
>>>>>>
>>>>>> Now I want to know where in the list or at what possition a specific
>>>> customer
>>>>>> is. If I sort by price, customer 3 is at place 2.
>>>>>> If I sort by points it would be at place 1.
>>>>>>
>>>>>> But right now there is no such information at which possition the
>>>>>> customer
>>>> is,
>>>>>> I
>>>>>> got this only by numbering the rows manually as described as my
>>>>>> previous
>>>> reply.
>>>>>> Now the problem is that everytime I want to get a possiton I need
>>>>>> to query
>>>> the
>>>>>> whole table with a different ORDER BY. And then number the rows
>>>>>> manually.
>>>>>> If the table has 10000 rows you have to number 10000 rows. And you
>>>>>> have the
>>>>>> data
>>>>> >from 10000 rows.
>>>>>> Image if the customer is at possition 3 you could only number until
>>>>>> you
>>>> reach
>>>>>> this specific customer AND do not have to number all the 10000
>>>>>> rows, just to
>>>>>> find out that the customer is a row 3. numbering all the other 9997
>>>>>> rows is
>>>>>> useless and a wast of data which is pulled from the table.
>>>>>>
>>>>>> So my question is if I can create a query which just does this without
>>>> getting
>>>>>> all the data.
>>>>>>
>>>>>> I hope it is more clear now. But english is not my primary language.
>>>>> Yes, *that* much is clear. What is still unclear is why you think it
>>>>> is important to know what position that customer is in the table.
>>>> see my reply to Captain Paralytic
>>> Oh, that would be the reply in which you fail -- for the fifth time,
>>> at least -- to answer the question "why do you need this information?"
>>>
>>> I'm going to repeat -- ONCE -- the advice I gave you in my first
>>> response, with some elaboration: You almost assuredly do not need to
>>> know this. That you think you do is strongly sugggestive of a flawed
>>> design, and I recommend that you re-examine the entire concept. There
>>> is almost certainly a simpler method of accomplishing your purpose,
>>> whatever it is -- but since you won't tell us what that purpose is,
>>> it's pretty hard to help you achieve it. And *that* suggests that you
>>> don't clearly understand, yourself, what that purpose is.
>> Doug, please see my earlier post. Rankings such as this are quite
>> common in many different areas.
>>
>> Rather than tell him why he doesn't need the information, maybe you
>> could help him with an answer. I'm trying to figure out how to do it
>> without updating a table each time. It's an interesting problem...
>>
>
> see the reply from nemaC.
>
> so far my tests went, everything was correct.
> Or do I miss something ?
>
> regards,
> johannes keßler
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.14 (GNU/Linux)
>
> iEYEARECAAYFAktwK30ACgkQE++2Zdc7Etff+gCdEIHKo6Gf58 3OfMvow3BR6HOk
> 4SQAn0+jvRElyA6hbOJG0G+k4XQ242gP
> =2gzo
> -----END PGP SIGNATURE-----

Yes, that's a much more elegant solution than the one I was looking at.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> Banana wrote:
>> That would be considerably faster than doing a correlated subqueries,
>> a traditional SQL solution for ranking, I would think.
>
> Not necessarily. Your solution requires retrieving multiple rows of
> data which are just thrown away. This probably would be faster if the
> user is near the top of the list, but could be slower if the user is far
> down the rankings. A subquery would only retrieve the requested row,
> but would have to number all rows.

Ah, if we didn't want all the rows, sure. I had assumed we would want
all of the rows that was examined to be returned and leave it up to
WHERE clause to tell when to stop examining. For something in the middle
without the leading rows, we'd probably need to look at it differently.

I wouldn't want to get rows just to toss it aside. God get quite irate
when data is wasted, you know.

On Mon, 08 Feb 2010 08:55:46 -0800, Banana wrote:
> Jerry Stuckle wrote:
>> Banana wrote:
>>> That would be considerably faster than doing a correlated subqueries,
>>> a traditional SQL solution for ranking, I would think.
>>
>> Not necessarily. Your solution requires retrieving multiple rows of
>> data which are just thrown away. This probably would be faster if the
>> user is near the top of the list, but could be slower if the user is far
>> down the rankings. A subquery would only retrieve the requested row,
>> but would have to number all rows.
>
> Ah, if we didn't want all the rows, sure. I had assumed we would want
> all of the rows that was examined to be returned and leave it up to
> WHERE clause to tell when to stop examining. For something in the middle
> without the leading rows, we'd probably need to look at it differently.
>
> I wouldn't want to get rows just to toss it aside. God get quite irate
> when data is wasted, you know.

I take it you'll be on the side against premature optimization the next
time some enthusiastic idiot pops in asking which of two near-identical
functions is faster outside of a loop, then? (:

--
77. If I have a fit of temporary insanity and decide to give the hero
the chance to reject a job as my trusted lieutentant, I will retain
enough sanity to wait until my current trusted lieutenant is out of
earshot before making the offer. --Anspach's Evil Overlord List

OP:
I assume your problem is similar with my case, which is :
MMORPG with about 1giga player with three attributes
(agility,strength,intel).
For each attribute the possible values are 0 to 99 (with interval
0,1).

Based on user's selected attribute, I want to show the user at which
position he/she is in the game.
For example: my strength is 20.56 and based on strength I am ranked 60
on the list.

For this problem you can solve wtih two query.
First.
Select count(1) from player where str < 20.56

Put the result in var a.

Then second query :
select count(1) from player where str = 20.56
Put the result in var b

So my ranking is ranged from a to (a+b)

Nb: you can also modify second query using order by user_id asc