CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
user1591668
user1591668

Reputation: 2893

Postgres Sql How can I get this ranking order with correct sequence

I have 2 Tables Profiles and Circles . The profiles table have a column called points . I am trying to do a ranking essentially get all the people that you are following and rank them from top to bottom according to points . That is working, my issue is that I also want to include the actual person doing the following in that rank . These pictures of my tables should clarify

Circles table The person with my_id is following other_id enter image description here

Profiles Table enter image description here

My ID is 57 below and I am following 59 with my query below the output is 14 since that's how many points user id 59 has . I would like to also include my own record in that query below, so that I could see how I rank along side the people I am following . The query should return 14 and 3 instead of just 14 now . Any suggestions would be great .

   Select p.points from profiles p
     join circles c on p.ID = c.other_id
     where c.my_id=57 order by p.points desc

Upvotes: 1

Views: 161

Answers (2)

igr
igr

Reputation: 3499

If I understand correctly, it can be something like

select (select points from profiles where id = c.my_id ) my_points, 
  (select points from profiles where id = c.other_id) other_points
from circles c
where my_id = 57
order by 1 desc,2 desc

NOTES:
1) Supposed that relatively low number of lines in circles for given condition. If that is not the case, it may be better to add info by joining profiles table
2) I supposed that you want points from other_id, not a rank

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

We can try introducing a compute column in a CTE which keeps track of whether a given user corresponds to the user doing the ranking. Then, select from that CTE and order using the computed column.

WITH cte AS (
    SELECT p.ID, p.points,
        CASE WHEN c.my_id = 57 THEN 0 ELSE 1 END AS position
    FROM profiles p
    INNER JOIN circles c
        ON p.ID = c.other_id
)

SELECT ID, points
FROM cte
ORDER BY position, points DESC;

One edge case here is that the user performing the ranking would not appear in the list below. If you actually would want the ranking user to appear both in the list and on the top, then you will have to use a union:

SELECT p.ID, p.points, 0 AS position
FROM profiles p
INNER JOIN circles c
    ON p.ID = c.other_id
WHERE c.my_id = 57
UNION ALL
SELECT p.ID, p.points, 1
FROM profiles p
INNER JOIN circles c
    ON p.ID = c.other_id
ORDER BY
    3, 2 DESC;

Upvotes: 1

Related Questions