kamille kamille - 7 months ago 8
SQL Question

How to get the ranks of two different colums in a table in my case?

So I have a table named db_points.

----------------------------------------
| Name | Points | SPoints | RPoints |
----------------------------------------
| Max | 240 | 50 | 1242 |
| Alvin | 600 | 123 | 3012 |
| Amanda | 234 | 1000 | 132 |
| Angela | 50 | 514 | 4023 |
| Rudolph | 2000 | 230 | 1232 |
----------------------------------------


I need the rank based on ordering by TPoints (Points+SPoints), Points, SPoints, the rank based on RPoints and RPoints.

This would look like the following for Angela, Rudolph or Amanda.

--------------------------------------------------------------------
| RankT | Name | TPoints | Points | SPoints | RankR | RPoints |
--------------------------------------------------------------------
| 4 | Angela | 564 | 50 | 514 | 5 | 4023 |
| 1 | Rudolph | 2230 | 2000 | 230 | 2 | 1232 |
| 2 | Amanda | 1234 | 234 | 1000 | 1 | 132 |
--------------------------------------------------------------------


As you see the rankings are different. The higher TPoints, the better RankT; the lower RPoints, the better RankR.

However I would need to get this in one SQL-query. This is what I got so far:

select Rank,
Name,
TPoints,
Points,
SPoints
RankR
from (select (@pos := @pos+1) pos,
(@rank := IF(@prev = TPoints,@rank,@pos)) Rank,
TPoints,
Points,
SPoints,
Name,
RPoints,
select @rownum:=@rownum + 1 as RankR,t.*
from (select (@prev := SPoints+Points) TPoints,
SPoints,
Points,
Name,
RPoints
from db_points
order by TPoints desc)

as ll,
(select RPoints,
Name
from db_points where RPoints > 0 order by RPoints ASC) t,(SELECT @rownum := 0)
r)
as l
where Name = '%s';


Hope that is understandable.

So far I have two working querys I need to put in one

select Rank, Name, TPoints, Points, SPoints from (select (@pos := @pos+1) pos, (@rank := IF(@prev = TPoints,@rank,@pos)) Rank, TPoints, Points, SPoints, Name from (select (@prev := SPoints+Points) TPoints, SPoints, Points, Name from db_points order by TPoints desc) as ll) as l where Name = '%s';


And this one

select RankR
from (select @rownum:=@rownum + 1 as RankR,t.*
from (select RPoints,
Name
from db_points where RPoints > 0 order by RPoints ASC) t,(SELECT @rownum := 0)
r)
a

where Name='%s';


Hope you can read the code, im bad in formating mysql code.

Answer

please give this a try

SELECT @rankT:=@rankT+1 as RankT,
       T.*
FROM
  (SELECT Name,
         (Points+Spoints) as TPoints,
         Points,
         Spoints,
         @rankR := @rankR + 1 as RankR,
         Rpoints
  FROM db_points p, (SELECT @rankR:=0)initialR
  ORDER BY Rpoints ASC
  )T,(SELECT @rankT:=0)intialT
ORDER BY TPoints DESC

sqlfiddle

Then you can put this whole query as a subquery and do your outer select to look for WHERE name like '%s'