Stack Overflow
Stack Overflow

Questions tagged [dense-rank]

A SQL window-function that returns the rank of each row within a result set partition, with no gaps in the ranking values.

dense_rank() is a window, or analytic, function, which computes the rank of each row in a query, within a partition of a result-set and based on the values in the ORDER BY clause. Unlike rank() there will never be any gaps in the ranking.

For SQL Server

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

For Oracle

DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

Questions tagged should also be tagged with the appropriate RDBMS, for instance or and, if appropriate, the specific version.

Documentation:

350 questions
4
votes
3 answers

Islands and Gaps Issue

Backstory: I have a database that has data points of drivers in trucks which also contain the. While in a truck, the driver can have a 'driverstatus'. What I'd like to do is group these statuses by driver, truck. As of now, I've tried using…
Black Dynamite
  • 4,067
  • 5
  • 40
  • 75
4
votes
4 answers

Oracle SQL - DENSE_RANK

I have a Client data table, selected columns of which are shown below: Row_ID Client_ID Status_ID From_date To_date 1 123456 4 20/12/2007 18:02 20/12/2007 18:07 2 789087 4 20/12/2007 18:02 …
user2948208
  • 127
  • 3
  • 9
4
votes
2 answers

Join single row from a table in MySQL

I have two tables players and scores. I want to generate a report that looks something like this: player first score points foo 2010-05-20 19 bar 2010-04-15 29 baz 2010-02-04 …
maček
  • 76,434
  • 37
  • 167
  • 198
4
votes
2 answers

SQL Server: How to use dense_rank on one column, based on order by another column

I have got a table in SQL Server 2008 where I need alternating values for one column, say column alt. Duplicates in that column always need the same value, hence I was thinking about using the dense_rank function for this column alt via % 2. But…
spse
  • 284
  • 2
  • 11
3
votes
1 answer

Using RANK OVER PARTITION to Compare a Previous Row Result

I'm working with a dataset that contains (among other columns) a userID and startDate. The goal is to have a new column "isRehire" that compares their startDate with previous startDates. If the difference between startDates is within 1 year,…
Rahmor
  • 33
  • 5
3
votes
2 answers

DENSE_RANK on two columns, where one column are distinct values and the other has duplicate

I have the following scenario where I'm struggling to understand how to apply DENSE_RANK() to get the result I…
RoyalSwish
  • 1,503
  • 10
  • 31
  • 57
3
votes
2 answers

How to do a COUNT(DISTINCT) using window functions with a frame in SQL Server

Piggybacking this lovely question: Partition Function COUNT() OVER possible using DISTINCT I wish to calculate a moving count of distinct value. Something along the lines of: Count(distinct machine_id) over(partition by model order by _timestamp…
goidelg
  • 316
  • 2
  • 16
3
votes
1 answer

MySQL update same table field from DENSE_RANK() results

i am trying to update field rank using DENSE_RANK() from same table,while trying to execute below query getting "Operand should contain 1 column(s)",kindly help in solving this issue UPDATE scoretable SET rank= (SELECT *,DENSE_RANK() OVER (PARTITION…
sathish kumar
  • 63
  • 6
3
votes
2 answers

How to make Ranking based on Year and Month?

I am trying to put Rank based on Year and Month. I have records like Products ProdID Years Months Quantity -------------------------------- 1652 2018 10 2 1010 2018 9 2 2236 2018 10 2 2236 …
Liam neesan
  • 2,282
  • 6
  • 33
  • 72
3
votes
2 answers

Remove Partial Duplicate Rows in SQL Server 2016

I have a data set that has some column where values match, but the rest of the column values do not. I need to delete duplicates where SubCategory of a lower level (Level2, Level3 and Level 4) "IS NOT NULL" but its corresponding "duplicate partner"…
Data Engineer
  • 795
  • 16
  • 41
3
votes
1 answer

Increment row number on group with repeated groups

I am working on a query for PLSQL (Oracle 12), in which I need to increment the row number of a dataset for each group. My problem is similar to this question (Increment Row Number on Group). However, I have a slightly different scenario. I have…
Mikel Urkia
  • 2,087
  • 1
  • 23
  • 40
3
votes
1 answer

SQL Rank while keeping time sorted

Given this data Type Time Outcome Wanted Result 1 8:00 1 1 1 9:00 1 1 1 10:00 1 1 0 11:00 2 2 0 12:00 2 2 0 13:00 2 2 1 14:00 1 3 1 15:00 1 3 0 16:00 2 4 1 17:00 1 5 0 18:00 2 …
rprecioso
  • 53
  • 3
3
votes
1 answer

Oracle slow RANK function

My application uses views that must be kept generic (no filters), and which include analytic functions RANK and DENSE_RANK. For example I have a view MYVIEW: SELECT RANK() OVER (PARTITION BY FIELD1 ORDER BY FIELD2) RANK, FIELD2, FIELD3 FROM…
PWW
  • 31
  • 1
  • 3
3
votes
1 answer

Split intervals of numbers into groups

I have the sequence of numbers below and I want to make a new group of numbers every time the following number has a difference of more than one unit from the previous value, i.e. breaking the continuous interval Table T value 1…
Roni Castro
  • 1,968
  • 21
  • 40
3
votes
1 answer

Restart Row_Number sequence when condition met

I'm running into a roadblock that seems like a simple fix, but I can't seem to figure out a solution. I'm hoping some folks on here with a bit more knowledge in Oracle SQL can help. Essentially, I am building a list of jobs ranked by complete date,…
Danny P.
  • 33
  • 1
  • 3
1
2
3
…
23 24