rank
What does the := operator mean in mysql?
In MySQL, := is an assignment operator: SELECT @foo := ‘bar’; // variable ‘foo’ now has value ‘bar’ return value: ‘bar’ while = is an equality test: SELECT @foo = ‘hi mom’; // does variable ‘foo’ have the value ‘hi mom’; return value: false (‘bar’ == ‘hi mom’ -> false) Note that you CAN do … Read more
Pandas rank by column value [duplicate]
Here’s one way to do it in Pandas-way You could groupby on Auction_ID and take rank() on Bid_Price with ascending=False In [68]: df[‘Auction_Rank’] = df.groupby(‘Auction_ID’)[‘Bid_Price’].rank(ascending=False) In [69]: df Out[69]: Auction_ID Bid_Price Auction_Rank 0 123 9 1 1 123 7 2 2 123 6 3 3 123 2 4 4 124 3 1 5 124 2 … Read more
Number rows within group in increasing order in a pandas dataframe
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
Get records with highest/smallest per group
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
Rank function in MySQL
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
ROW_NUMBER() in MySQL
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