get the record position in query result

vahidreza · #1 (**permalink**) 06-10-09, 04:32

Hi, first take a look to this simple table

ID,Name,Points
1,A.Smith,18
2,M.James,19
3,S.Fernandez,17
4,A.Sweet,23

i need a query to sort the table by Points Column and give me the rank of ID=3 that in this example should be 4.

ashish_mat1979 · #2 (**permalink**) 06-10-09, 07:17

SET @a=0;
SELECT (@a:=@a+1) as Rank, Name, Points, ID FROM `MyTableName` order by Points desc;

vahidreza · #3 (**permalink**) 06-10-09, 09:41

Hi, this query returns all records with record number ! but i just want to have record Id = 3 with it's rank !

suppose i have 20000 records and should loop thru it to find the rank of id = 3 ? or use filters ? , i don't want to load all the data !!

r937 · #4 (**permalink**) 06-10-09, 10:19

Code:

SELECT t.ID
     , t.Name
     , t.Points
     , ( SELECT COUNT(*) + 1
           FROM daTable
          WHERE Points > t.Points ) AS rank
  FROM daTable AS t
 WHERE t.ID = 3

vahidreza · #5 (**permalink**) 06-11-09, 07:18

yeah , it works. Nice Idea !

vahidreza · #6 (**permalink**) 06-11-09, 09:48

r937, it was a very nice trick but there is something that coulnt make it !

maybe we have such table :
ID,Name,Points
1,A.Smith,18
2,M.James,18
3,S.Fernandez,17

if we query for id=3 , we will get 3 but S.Fernandez is the second top point.
what do you think about this issue ? or any idea friends ?

r937 · #7 (**permalink**) 06-11-09, 11:40

what you describe is the difference between rank and dense rank

S.Fernandez is the second top point, but the third person in point rank

try replacing COUNT(*) with COUNT(DISTINCT Points) in the subquery

Haydn · #8 (**permalink**) 04-03-12, 09:41

I searched and found this fantastic query:

Quote:

Originally Posted by r937

Code:

SELECT t.ID
     , t.Name
     , t.Points
     , ( SELECT COUNT(*) + 1
           FROM daTable
          WHERE Points > t.Points ) AS rank
  FROM daTable AS t
 WHERE t.ID = 3

Now, if I would like the sum of points instead, how could I do then?

I tried this,

Code:

SELECT t.ID
     , t.Name
     , SUM(t.Points) AS sumPoints
     , ( SELECT COUNT(*) + 1
           FROM daTable
          WHERE Points > t.sumPoints) AS rank
  FROM daTable AS t
 WHERE t.ID = 3
GROUP BY t.ID

but then I get the "Unknown column 't.sumPoints' in 'where clause'".

Thanks.

r937 · #9 (**permalink**) 04-03-12, 16:34

Quote:

Originally Posted by Haydn

I searched and found this fantastic query:

oh, you are too kind

Quote:

Originally Posted by Haydn

Now, if I would like the sum of points instead, how could I do then?

i need to understand what you're asking for

could you please give some comprehensive sample data, and show the results that you want from that data

Haydn · #10 (**permalink**) 04-04-12, 03:58

Quote:

Originally Posted by r937

oh, you are too kind

i need to understand what you're asking for

could you please give some comprehensive sample data, and show the results that you want from that data

Ok, if I take the authors example, but instead of ID being unique, it could be multiple records for each ID (I skipped the name here).

ID,Points
1, 5
2, 8
3, 9
2, 5
1, 6
4, 8
3, 1

So he sum of the different ID's would mean that ID 2 is ranked #1 (13 points), ID 1 is ranked #2 (11 points), ID 3 is ranked #3 (10 points) and ID 4 is ranked #4 (8 points).

The result I want is the rank for e.g. ID 3, in the above case 3.

I hope that is understandable, thanks.

r937 · #11 (**permalink**) 04-04-12, 16:07

Quote:

Originally Posted by Haydn

I hope that is understandable, thanks.

yup, it is now

all we have to do is take the previous solution...

Code:

SELECT t.ID
     , t.Points
     , ( SELECT COUNT(*) + 1
           FROM daTable
          WHERE Points > t.Points ) AS rank
  FROM daTable AS t
 WHERE t.ID = 3

and instead of using the actual table (daTable), we use a subquery instead...

Code:

SELECT t.ID
     , t.sumpoints
     , ( SELECT COUNT(*) + 1
           FROM ( SELECT id
                       , SUM(points) AS sumpoints
                    FROM daTable 
                  GROUP
                      BY id ) AS t2
          WHERE sumpoints > t.sumpoints ) AS rank
  FROM ( SELECT id
              , SUM(points) AS sumpoints
           FROM daTable 
         GROUP
             BY id ) AS t
 WHERE t.ID = 3

Haydn · #12 (**permalink**) 04-05-12, 04:21

Quote:

Originally Posted by r937

yup, it is now

all we have to do is take the previous solution...

Code:

SELECT t.ID
     , t.Points
     , ( SELECT COUNT(*) + 1
           FROM daTable
          WHERE Points > t.Points ) AS rank
  FROM daTable AS t
 WHERE t.ID = 3

and instead of using the actual table (daTable), we use a subquery instead...

Code:

SELECT t.ID
     , t.sumpoints
     , ( SELECT COUNT(*) + 1
           FROM ( SELECT id
                       , SUM(points) AS sumpoints
                    FROM daTable 
                  GROUP
                      BY id ) AS t2
          WHERE sumpoints > t.sumpoints ) AS rank
  FROM ( SELECT id
              , SUM(points) AS sumpoints
           FROM daTable 
         GROUP
             BY id ) AS t
 WHERE t.ID = 3

How good are you!? Works perfect, thanks a lot!

I admit I don't fully grasp this query though. I'll spend more with it. I might need to JOIN in this later as well, but I'll get back here if I run into problems.

Thanks again.

Internet Services

Web Development

Digital Marketing

Consumer Tech