The good news is that you can throw away the SUMPRODUCT function and replace it with a pair of COUNTIFS functions. The COUNTIFS can use full column references without detriment and is vastly more efficient than the SUMPRODUCT even with the SUMPRODUCT cell ranges limited to the extents of the data. In N2 as a … Read more
The generic way to do that is to group the desired fiels in a tuple, whatever the types. df[“Rank”] = df[[“SaleCount”,”TotalRevenue”]].apply(tuple,axis=1)\ .rank(method=’dense’,ascending=False).astype(int) df.sort_values(“Rank”) TotalRevenue Date SaleCount shops Rank 1 9000 2016-12-02 100 S2 1 5 2000 2016-12-02 100 S8 2 3 750 2016-12-02 35 S5 3 2 1000 2016-12-02 30 S1 4 7 600 2016-12-02 … Read more
Try this query: – MySql does not support Rank() function. select result.id,result.login,result.rank from ( SELECT id, login, IF(login=@last,@curRank:=@curRank,@curRank:=@_sequence) AS rank, @_sequence:=@_sequence+1, @last:=login FROM ds , (SELECT @curRank := 1, @_sequence:=1, @last:=0) r ORDER BY id asc) as result; Hope it helps you!
Use groupby/cumcount: In [25]: df[‘C’] = df.groupby([‘A’,’B’]).cumcount()+1; df Out[25]: A B C 0 A a 1 1 A a 2 2 A b 1 3 B a 1 4 B a 2 5 B a 3
Use groupby/cumcount: In [25]: df[‘C’] = df.groupby([‘A’,’B’]).cumcount()+1; df Out[25]: A B C 0 A a 1 1 A a 2 2 A b 1 3 B a 1 4 B a 2 5 B a 3
SELECT uo.*, ( SELECT COUNT(*) FROM users ui WHERE (ui.points, ui.id) >= (uo.points, uo.id) ) AS rank FROM users uo WHERE id = @id Dense rank: SELECT uo.*, ( SELECT COUNT(DISTINCT ui.points) FROM users ui WHERE ui.points >= uo.points ) AS rank FROM users uo WHERE id = @id
So you want to get the row with the highest OrderField per group? I’d do it this way: SELECT t1.* FROM `Table` AS t1 LEFT OUTER JOIN `Table` AS t2 ON t1.GroupId = t2.GroupId AND t1.OrderField < t2.OrderField WHERE t2.GroupId IS NULL ORDER BY t1.OrderField; // not needed! (note by Tomas) (EDIT by Tomas: If … Read more
One option is to use a ranking variable, such as the following: SELECT first_name, age, gender, @curRank := @curRank + 1 AS rank FROM person p, (SELECT @curRank := 0) r ORDER BY age; The (SELECT @curRank := 0) part allows the variable initialization without requiring a separate SET command. Test case: CREATE TABLE person … Read more
There is no ranking functionality in MySQL. The closest you can get is to use a variable: SELECT t.*, @rownum := @rownum + 1 AS rank FROM YOUR_TABLE t, (SELECT @rownum := 0) r so how would that work in my case? I’d need two variables, one for each of col1 and col2? Col2 would … Read more