* Urmas Buhvestov
I have MySQL database with data like
name points
a20
b20
c19
d18
e18
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.pointst1.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
