* Urmas Buhvestov
> I have MySQL database with data like
> name points
> a 20
> b 20
> c 19
> d 18
> e 18
use test;
create table MyTable (name char(1), points int);
insert into MyTable values ('a',20),('b',20),('c',19),('d',18),('e',18);
> is it possible to get with mysql commands ONLY outpt
>
> place repeatings name points
> 1 2 a 20
> 1 2 b 20
> 3 1 c 19
> 4 2 d 18
> 4 2 e 18
>
> </pre>
> first column shows how many persons have beter points +1
> second column shows how many records have same number of points
Try a self join.
This will only work on a small amount of data, because each row is joined
with every other row in the table with higher or same score. This will take
a lot of time on a big table. If this becomes a problem for you, try adding
a WHERE clause limiting the number of rows examined, for instance "WHERE
t1.points > 15".
select
sum(t2.points>t1.points)+1 place,
sum(t2.points=t1.points)+1 repeatings,
t1.name,
t1.points
FROM MyTable t1
LEFT JOIN MyTable t2 ON
t2.name <> t1.name AND
t2.points >= t1.points
GROUP BY t1.name,t1.points
ORDER BY t1.points DESC;
+-------+------------+------+--------+
| place | repeatings | name | points |
+-------+------------+------+--------+
| 1 | 2 | a | 20 |
| 1 | 2 | b | 20 |
| 3 | 1 | c | 19 |
| 4 | 2 | d | 18 |
| 4 | 2 | e | 18 |
+-------+------------+------+--------+
5 rows in set (0.00 sec)
HTH,
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
