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
1
vote
3 answers
Sql Server - Counting partitions with Ranking functions
Consider the following sample data:
Name Email RowRank PartitionRank
---- ----- ------- -------------
a1 e1 1 1
a1 e1 2 1
a2 e2 1 2
a2 e2 2 2
a2 e2 3 …
r_honey
- 883
- 4
- 15
- 31
1
vote
1 answer
Select MAX and add 1 (SQL Server 2012)
DROP TABLE #ID
CREATE TABLE #ID (ID INT)
INSERT INTO #ID (ID)
VALUES (24),(65),(77),(44)
DECLARE @ID int
SELECT @ID = MAX(ID) from #ID
DROP TABLE #name
CREATE TABLE #NAME (Name char (20))
INSERT INTO #NAME (name)
VALUES ('Ben'),('Alex'),('Mark')…
Bhupinder Singh
- 1,061
- 1
- 11
- 21
0
votes
2 answers
EXCEL rank.eq function: rank numbers in a row in relation to other numbers of the same assigned category
MY Spreadsheet was made using Excel 2016. I have a table with different numbers with assigned categories:
Size
Category
11
solid
12
liquid
11
liquid
7
liquid
9
solid
I want to return the rank of the numbers, but only rank them…
new
- 3
- 2
0
votes
1 answer
How to rank based on certain column value oracle SQL
select * from
(
SELECT NORM_VERSION, NORM_FULL_VERSION, NORM_PRODUCT_VALUE, LIFECYCLE_TYPE, START_DATE,
rank() OVER (PARTITION BY NORM_VERSION, NORM_FULL_VERSION,
NORM_PRODUCT_VALUE,
…
maddy
- 45
- 1
- 6
0
votes
0 answers
Develop a Ranking based On filtered Data (Static & Dynamic)
I created a measure that will rank universities based on the total score they obtained from multiple projects. This ranking is dynamic based on the applied filters such as Year and Journal.
Suppose a user filters for a specific university using the…
Sina Aghaie
- 1
- 1
0
votes
0 answers
Trouble with RANK() function in SQL Server for partitioned sum, what am I doing wrong?
I am new to RANK() and for the life of me I cannot get figure out what I am doing wrong. I want the sum of InvoiceAmt partitioned by year like I have already, but I also want to rank by Total_by_yr.
Below is my code the results.
SELECT c.CustNum,…
brad65340
- 1
- 1
0
votes
1 answer
NDCG-score in top-k recommendations
Rating prediction taks in Recommender Systems.
I have prepared a custom data set, and I applied models to predict rating which user gave to the item.
I keep getting the results, where NDCG-5 is higher than NDCG-3.
Is it really possible?
Attached, I…
Yustina Ivanova
- 122
- 9
0
votes
2 answers
Conditional aggregation in SQL Server identifying the latest non NULL entry
Starting Point:
I have a large table with many columns, of which the first is an identifier (ID) and the second a reversed rank over this ID (Rank). Each ID can have an arbitrary number of rows displaying either a NULL or a float value in each…
HappyHippo
- 13
- 4
0
votes
1 answer
Pine code v5, the rank and the abs functions not recognised
this pinescript v5 doesn't seem to work for me, do you see anything wrong, how to correct it?
//@version=5
indicator("Top 20 Candles with Highest Absolute Differences", overlay=true)
// calculate the absolute difference between open and close…
Lavy
- 1
0
votes
1 answer
q kdb rank function with duplicated ranks
What would be an implementation of a q kdb function that does the same thing as the rank function but returns duplicated rank numbers for equal values?
In the standard implementation
rank 2 7 4 3 19 16 15 20 25 16 19
gives:
0 3 2 1 7 5 4 9 10 6…
crogg01
- 2,446
- 15
- 35
0
votes
0 answers
Additional column with values as column identifier which has the highest value
SELECT *, 'success' as status
FROM crosstab(
'SELECT ip_address,id,count(*) as count from table_1 where code= ''200'' group by 1,2 order by 1,2',
'select COALESCE(id,''null'') from public.table_1 group by id order by id asc')
AS…
Ramya Mahe
- 19
- 2
0
votes
1 answer
Is it right to use DISTINCT and RANK() function together for distinct values in a select statement?
Can we use DISTINCT and RANK() function together in a select statement for distinct values? Lets take 2 scenarios
1.Using RANK() and DISTINCT
2.Using only RANK() .
In these both scenarios will we get same distinct values?
SELECT * FROM (
SELECT…
Logan
- 13
- 2
0
votes
0 answers
Oracle: Can’t figure out right syntax to rank group of records and filter ones I don’t need
I have a table that appears to have duplicate rows except for two id columns. I tried ranking based on a partition, I tried row_number based on a partition and I’m just not able to get the syntax correct. Below is a sample data set.
Smn_key
Case…
CreationSL
- 55
- 1
- 11
0
votes
0 answers
In Tableau, how can you group rankings based on criteria (i.e. Top 25, 26-50, 51-100) instead of having to show all distinct fields being ranked?
I have data showing City and sum of order count.
There are specific categories I'd like these rankings to be produced using (below):
Year-2023; Category-Farming; Order(count)
My output just shows Rank, City, and Order Count. I have Category and Year…
Panthersfball234
- 95
- 2
- 10
0
votes
0 answers
Rank based on conditional Partitions in Snowflake
I have a table1 which has a d_date column and table_2 which has start and end_dates columns and a code column , after joining this matches with the first table on at start and end date values :
So I got below result after joining these two tables…
Priya Chauhan
- 445
- 1
- 5
- 21