select users.name
from(
select users.name, pay.uid, 
RANK() OVER(ORDER BY count(pay.uid) DESC) AS ranking
from pay, users
where users.uid = pay.uid
group by users.uid)
where ranking = 1

i only want to get users.name output not name,uid,rank.

CodePudding user response：

SELECT sub.name
FROM (SELECT users.name, pay.uid, 
      RANK() OVER(ORDER BY count(pay.uid) DESC) AS ranking
      FROM pay
      INNER JOIN users
        on users.uid = pay.uid
      GROUP BY users.uid) sub
WHERE ranking = 1

• alias the inline view (sub)
• change alias on outer select to sub instead of pay
  • The outer query has no knowledge of the tables inside thus the sub alias.
• avoid using , for joins that's a 1980s technique the newer standars are to use joins (inner, outer (left, right full), cross etc.)

CodePudding user response：

If i read your problem You can used this query

select table.name from(select users.name, loans.uid, RANK() OVER(ORDER BY count(loans.uid) DESC) AS ranking from loans left join users on users.uid = loans.uid group by users.uid) as 'table' where ranking = 1