开发者

SQL Query to find the uid of the person with max(status_count+follower_count) and whose is_my_friend = 1

I have created a table with the following structure-

$sql = "CREATE TABLE followers
    (
     uid int UNSIGNED NOT NULL UNIQUE,
     PRIMARY KEY(uid),
     follower_count int UNSIGNED ,
     is_my_friend bool,
     status_count int UNSIGNED,
   开发者_如何学JAVA  location varchar(50)
    )";

I need to find the uid of the person with max(status_count+follower_count) and whose is_my_friend = 1

I wrote the following query but I ain't getting the correct uid.

SELECT p.uid FROM (select uid,is_my_friend,max(follower_count+status_count) from followers) p WHERE p.is_my_friend = 1;


The following query will work:

Select uid
From followers
Where is_my_friend = 1
Order By (follower_count+status_count) desc LIMIT 0,1

Limit 0,1 works in MySql.

Or, if you want to return all rows where follower_count+status_count = max only, this is the query:

Select uid
From followers
Where is_my_friend = 1
  And (follower_count+status_count) = (select max(follower_count+status_count)
                                       from followers
                                       where is_my_friend = 1)


SECLECT uid FROM followers ORDER BY (follower_count + status_count) DESC WHERE is_my_friend = 1 


SELECT top 1 uid (follower_count+status_count) as totalcount FROM followers WHERE p.is_my_friend = 1 order by totalcount desc

I am not 100% if that order by is possible. Try it out, if not create a view that combines those fields

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜