Count the number of records in SQL results before a certain value is reached

Looking to count the number of SQL records until a certain value in one of the table columns is reached. Using Asp /VBscript.

Not sure how to formulate the query.

What I’ve tried is but of course it isn’t working. What I’d like is to organise the results by the column “points” in decending order, grouped by the city_id “12500”. I then need the number of records counted until a specific “member_id” is matched. Some “points” values could also be the same, which can cause an incorrect count. Similar points values should be ignored.

db named “rankings” looks like this:

id  |  member_id  |  city_id  |  points
1   |      1      |   12500   |    2
3   |      2      |   12500   |    5
4   |     34      |     800   |    1
5   |     14      |   12500   |    14
6   |      6      |     600   |    12
7   |     11      |   12500   |    11
8   |     12      |   12500   |    5

For example, if I want to find the ranking for member_id “2”, who happens to belong to city_id “12500”, the correct final value from the Count function should be 3. This is because member_id “2” has the third highest points value in city_id “12500”, even after taking into account the tie in points with member_id “12”.

This below is all I can think of as I’m not a pro by any means and I know it’s missing a lot!

member_id = 2
city_id = 12500

SELECT Count() as city_ranking FROM (SELECT * from rankings WHERE city_id='"&city_id&"' AND member_id <> '"&member_id&"' ORDER BY points DESC)

Answer

You can get the rank for a particular member by doing:

select count(distinct points)
from ranking r cross join
     (select r.*
      from ranking r
      where member_id = 5
     ) rm
where r.points >= rm.points and
      r.city_id = rm.city_id;

Although you can express this with window functions, it is a bit fidgety because you have to be careful about where you place your filters.

Advertisement

Answer