Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are non-deterministic.
Questions tagged [ranking-functions]
193 questions
2
votes
1 answer
SAS Proc SQL - ranking top nth (3rd) highest for a group of say universities and their price? (HW to be honest)
(this is homework, not going to lie)
I have an ANSI SQL query I wrote
this produces
the required
3rd highest prices correctly,
table sample is
select unique uni, price
from
(
(
select unique uni, price
from
(
select unique…
Ray Man
- 65
- 11
2
votes
3 answers
Adding Conditional Clause(Where) to Dense Rank Function
I want to create an Rank function to count the number of times a person a visited to property BY DATE but with condition of not including a visit category. 'Calls'
DENSE_RANK() over(partition by activitytable.[Property]
ORDER BY…
shevchenko2020
- 23
- 1
- 7
2
votes
2 answers
For each unique ID, I would like to have only one row based on a ranking of another field
Ultimately, I am looking to create a table that contains each unique ID I have in my dataset with a corresponding field, based on a "ranking" of that field.
I've been very stuck on this step. I have considered looping for each unique ID, but wanted…
K.C.
- 61
- 7
2
votes
1 answer
How to do Sliding Window Rank in Spark using Scala?
I have a data set :
+-----+-------------------+---------------------+------------------+
|query|similar_queries |model_score |count |
+-----+-------------------+---------------------+------------------+
|shirt|funny shirt …
user3407267
- 1,524
- 9
- 30
- 57
2
votes
1 answer
SQL Running total group with sum threshold reset
I have the table :
PersonID FirstName PersonAge
1 Pras 2
2 Deep 3
3 Test 4
4 Prash 2
5 ser 1
6 df 8
7 ddf 5
8 …
Ace McCloud
- 798
- 9
- 27
2
votes
2 answers
Ranking Over Row_Number in SQL
I am posting a sample data below.
What I have is a row number which generated a number based on Date and Name columns (achieved using ROW_NUMBER function). What I need now, is another derived column called Group_Num which creates a number for each…
Triumph Spitfire
- 663
- 15
- 38
2
votes
1 answer
How to choose the OKAPI BM25 parameters : b and k1
I was actually wondering, how can we validate or evaluate empirically the values of b and k1 in the BM25 formulas? in other terms what is the most 'scientific' way to evaluate it?
Is there any research paper that we can refer to in order to see how…
sel
- 942
- 1
- 12
- 25
2
votes
1 answer
Update a MySQL table with record rankings within groups
I have a table called 'winners' with columns 'id', 'category', 'score', 'rank'.
I need to update my table and asign a rank within the subcategories (category_id) which as per the sample is 2 but could be more than that in the future.
Most anwers…
ConorBeckett
- 60
- 9
2
votes
0 answers
How to detect trending items with popularity count?
I made a search app with elasticsearch.
Items have name and follower count. I use follower count to boost elasticsearch result.
Ex: Let's say i have two item. item_1 = [name = "abc def", follower = 1000] and item_2 = [name = "abc", follower = 10].…
mehmet.onler
- 21
- 6
2
votes
3 answers
Complicated TSQL custom ranking task
I have tried a whole lot of variety of ranking solutions with joins and all to match the needs I want.
Sadly, I cannot come up with correct query to get the desired output.
I am really looking for any help to get explanation that would help me in…
Kirill Pashkov
- 3,118
- 1
- 15
- 20
2
votes
2 answers
Ranking two vectors of numbers relative to each other in MATLAB
I have two set of numbers and want to compare and rank them relative to each other in MATLAB.
The data is:
x = [3 7 8 25 33 52 64 65 78 79 91 93];
y = [7 10 12 27 30 33 57 62 80 83 85 90];
I started with the for/if/else commands and got stuck in…
user3770811
- 21
- 1
2
votes
1 answer
Selecting trading rules based on past performance
I am trying to develop a trading system using Quantmod, PerformanceAnalytics and Systematic Investors Toolbox.
I want to create and test a number of simple trading rules (Prices > SMA), (rsi 2 < 0.5 = long) etc etc (this part works fine) based on…
user2690001
- 23
- 3
2
votes
1 answer
Ranking variables with conditions
Say I have the following data frame:
df <- data.frame(store = LETTERS[1:8],
sales = c( 9, 128, 54, 66, 23, 132, 89, 70),
successRate = c(.80, .25, .54, .92, .85, .35, .54, .46))
I want to rank the…
Waldir Leoncio
- 10,853
- 19
- 77
- 107
2
votes
4 answers
Select one row per group without specifying order?
Suppose I have this data:
create table People (ID int identity, Name nvarchar(50), Age int);
insert into People values
('Bill Jones', 50),
('Bill Jones', 12),
('Sam Smith', 23),
('Jill Brown', 44),
('Jill Brown', 67),
('Jill Brown',…
Blorgbeard
- 101,031
- 48
- 228
- 272
2
votes
1 answer
Get a record position in a group + record count of the group on each record
I have a resultset in a temp table on SQL Server 2008. One of the fields is a group Id, and for each group I can have any number of rows. I'd like to get in a new column the position of the row in the group, such as "row 1 of 3"
So, if I have temp…
Alejandro B.
- 4,807
- 2
- 33
- 61