ranking - w3toppers.com

selecting top N rows for each group in a table

If you’re using SQL Server 2005 or newer, you can use the ranking functions and a CTE to achieve this: ;WITH HairColors AS (SELECT id, name, hair, score, ROW_NUMBER() OVER(PARTITION BY hair ORDER BY score DESC) as ‘RowNum’ ) SELECT id, name, hair, score FROM HairColors WHERE RowNum <= 3 This CTE will “partition” your … Read more

Row Rank in a MySQL View

Use: SELECT t.id, t.variety, (SELECT COUNT(*) FROM TABLE WHERE id < t.id) +1 AS NUM FROM TABLE t It’s not an ideal manner of doing this, because the query for the num value will execute for every row returned. A better idea would be to create a NUMBERS table, with a single column containing a … Read more

MySQL update statement to store ranking positions

Here’s a way to do it: SET @r=0; UPDATE table SET Ranking= @r:= (@r+1) ORDER BY Score DESC; /* use this if you just want to pull it from the db, but don’t update anything */ SET @r=0; SELECT *, @r:= (@r+1) as Ranking FROM table ORDER BY Score DESC;

Comparison-based ranking algorithm

This is a problem that has already occurred in another arena: competitive games! Here, too, the goal is to assign each player a global “rank” on the basis of a series of 1 vs. 1 comparisons. The difficulty, of course, is that the comparisons are not transitive (I take “subjective” to mean “provided by a … Read more

Find the n most common values in a vector [duplicate]

I’m sure this is a duplicate, but the answer is simple: sort(table(variable),decreasing=TRUE)[1:3]

What string similarity algorithms are there?

The Levenshtein distance is the algorithm I would recommend. It calculates the minimum number of operations you must do to change 1 string into another. The fewer changes means the strings are more similar…

How do I find the closest values in a Pandas series to an input number?

You could use argsort() like Say, input = 3 In [198]: input = 3 In [199]: df.iloc[(df[‘num’]-input).abs().argsort()[:2]] Out[199]: num 2 4 4 2 df_sort is the dataframe with 2 closest values. In [200]: df_sort = df.iloc[(df[‘num’]-input).abs().argsort()[:2]] For index, In [201]: df_sort.index.tolist() Out[201]: [2, 4] For values, In [202]: df_sort[‘num’].tolist() Out[202]: [4, 2] Detail, for the … Read more

A better similarity ranking algorithm for variable length strings

Simon White of Catalysoft wrote an article about a very clever algorithm that compares adjacent character pairs that works really well for my purposes: http://www.catalysoft.com/articles/StrikeAMatch.html Simon has a Java version of the algorithm and below I wrote a PL/Ruby version of it (taken from the plain ruby version done in the related forum entry comment … Read more