Create A View Which Gets Rank,username And Count Of User Records

I have a table below .I am unable to create view which displays count of records w.r.t user and username from another table and rank ,lets say for category '1'.How do I create a vi

Solution 1:

You were on the right track. Just needed to make minor changes. The following query will give you the desired results. In inner query got the first 4 columns and to get rank cross joined that to (SELECT @curRank := 0) r which is MySQL trick for getting rank. in the end just needed to order by Cnt to make it work.

SELECT username
    ,userid
    ,category
    ,Cnt
    ,@curRank := @curRank + 1 AS rank
    FROM (
            SELECT b.Username
                ,B.userid
                ,A.category
                ,count(*) Cnt
            FROM tblb B 
            JOIN tbla A
                ON B.UserID = A.User
            WHERE a.Category = 1
            GROUP BY b.username
        )a
,(SELECT @curRank := 0) r
Order by cnt desc

In order to put it into View you can use hack described by @Gordon-Linoff in this question

End code will look something like this.

CREATE VIEW TestView1
AS
    SELECT b.Username
           ,B.userid
           ,A.category
           ,COUNT(*) Cnt
        FROM tblb B
        JOIN tbla A
            ON B.UserID = A.User
        WHERE a.Category = 1
        GROUP BY b.username
        ORDER BY cnt DESC;

CREATE VIEW TestView2
AS
    SELECT t1.*
           ,( SELECT 1 + COUNT(*)
                FROM TestView1 AS t2
                WHERE t2.Cnt > t1.Cnt
                    OR (
                         t2.Cnt = t1.Cnt
                         AND t2.userid < t1.userid ) ) AS Rank
        FROM TestView1 AS t1

TestView1 is used to get first 4 columns that you defined. TestView2 you just select everything from first view and than add column that checks to see if value that you selecting is ether bigger or smaller than value in first instance of that view.

Solution 2:

I'm sure SaUce's answer's fine - but there still seems to be too much query there!

SELECT username
     , user
     , category
     , COUNT(*) num
     , @i:=@i+1 rank 
  FROM tbla a 
  JOIN tblB b 
    ON b.userid = a.user
     , (SELECT @i:=0)var 
 WHERE category = 1 
 GROUP 
    BY user 
 ORDER 
    BY num DESC;