Stack Overflow
Stack Overflow

Questions tagged [ranking-functions]

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.

193 questions
1
vote
1 answer

SQL Server : Grouping sets of duplicate rows

I have geographic products that may have duplicates. I return a list of possible duplicates and display them on a map for a user to check and delete. To help the user cross reference between rows, I want to colour code the duplicates. Two or more…
user2470281
  • 107
  • 9
1
vote
1 answer

How to find rank of order number by same start

Dok table contains order numbers : create table dok ( doktyyp char(1), tasudok char(25) ); CREATE INDEX dok_tasudok_idx ON dok (tasudok); CREATE UNIQUE INDEX dok_tasudok_unique_idx ON dok (doktyyp,tasudok) WHERE doktyyp IN ( 'T', 'U') ; Order…
Andrus
  • 26,339
  • 60
  • 204
  • 378
1
vote
1 answer

Rank() window function on Redshift over multiple columns , not 1 or 2

I want to use rank() window function on a Redshift database to rank over specific, multiple columns. The code shall check those multiple columns per each row and assign same rank to rows that have identical values in ALL those columns. Example image…
Sging
  • 27
  • 5
1
vote
1 answer

Partition By 2 Columns in BigQuery

Say I want to rank the top 3 months of revenue for each flat and the revenue amount, I can execute the following no problem: Select * FROM (SELECT Flat, EXTRACT(YEAR FROM pay_day) AS Year, EXTRACT(MONTH FROM pay_day) AS Month, RANK() OVER(PARTITION…
bran
  • 85
  • 2
  • 8
1
vote
2 answers

How to use rank to get rid of duplicate id?

I have the following two tables: id | age | sex | key | sport 293 22 m a soccer 987 25 f b track 501 27 m c swimming 293 22 m a soccer 098 31 f d soccer 501 27 m …
Meredith_Gray
  • 29
  • 4
1
vote
1 answer

How to compute a weighted ranking score?

Given the output of a ranking question: df <- structure(list(rank1 = c("A", "NA", "C", "B", "A"), rank2 = c("B", "NA", "A", "A", "B"), rank3 = c("C", "NA", "B", "C", "NA"), rank4 = c("D", "NA", "E", "D", "NA"), rank5 = c("E", "NA", "D", "E",…
Caro
  • 25
  • 3
1
vote
1 answer

Pivot and also get ranking

I am trying to write a SQL that can pivot and also get its ranking order all in one line. Not sure how. Example: Pivot the area but also get the ranking of the cost (order of most expensive) table name: aaa entity_id | entity_type | datetime |…
Jacky Lam
  • 67
  • 1
  • 2
  • 7
1
vote
2 answers

Rank aggregation: Merge local subrankings into global ranking

I have a dataset of multiple local store rankings that I'm looking to aggregate / combine into one national ranking, programmatically. I know that the local rankings are by sales volume, but I am not given the sales volume so must use the relative…
iOSBeginner
  • 363
  • 4
  • 17
1
vote
1 answer

Sum Consecutive Months Based on Groups with Criteria

I am having trouble narrowing down sales in top regions that occurred in consecutive months. I know I need to use some form of window function with Row_Number or Dense_Rank, but I am having trouble getting the final output Here is my source…
UserX
  • 157
  • 13
1
vote
1 answer

Partition a list of names into folders by initial letter

Given a set of data like: id Name 1 Aaa 2 Aab 3 AAc … 999 Zzz , I'd like to create virtual folders that partition it by initial letter. For example, I'd like to pass 7 to a function and get 7 folders,…
Sören Kuklau
  • 19,454
  • 7
  • 52
  • 86
1
vote
1 answer

UPDATE using Rank(), Row_Number excluding duplicate values

I've a dataset similar to the one below. I need to update the base lookup table based on the values provided in the updated_CustomerId column. The base tables is the same as the dataset but it does not have updated_CustomerId column. The…
Data Engineer
  • 795
  • 16
  • 41
1
vote
1 answer

How to rank a cell value within the same cell

I am trying to rank a set of numbers (in a column), but I want that rank to appear next to the number in the same cell. For example, if the number is "21" and that ranks "3rd" – I would want the cell to read "21 (3)". BONUS POINTS if I can format…
sebo620
  • 71
  • 7
1
vote
0 answers

How to skip defined Combinations in sql?

--A=100,B=50,C=200 DECLARE @T1 TABLE (CAT1 VARCHAR(1),CAT2 VARCHAR(1),MinVal int) INSERT INTO @T1 SELECT 'A','A',100 UNION ALL SELECT 'A','B',50 UNION ALL SELECT 'A','C',100 UNION ALL SELECT 'B','A',50 UNION ALL SELECT 'B','B',50 UNION ALL SELECT…
Bhanu Pratap singh
  • 45
  • 12
1
vote
1 answer

How can I RANK() OVER PARTITION BY with a WHERE Clause

I need to do 2 ranks in a table, one for all rows and one where Total Cum Production is greater than zero. Is there any way to do that with a Rank Function? SELECT LocationNumber ,[Date] ,Oil+Gas+Water as [TotalFluid] …
Ryan
  • 79
  • 1
  • 1
  • 7
1
vote
1 answer

How to get scores in output file from Tensorflow ranking sysytem?

I am not able to get the scores as output in a file for each row from the scoring function. I am using a scoring function to rank my documents in recommendation engine with Python in Jupyter notebook. I am using below code to evaluate on my test…
Abhishek V
  • 141
  • 1
  • 7
1 2 3
…
12 13