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
21
votes
5 answers

How to emulate SQLs rank functions in R?

What is the R equivalent of rank functions like the Oracle ROW_NUMBER(), RANK(), or DENSE_RANK() ("assign integer values to the rows depending on their order"; see http://www.orafaq.com/node/55)? I agree that the functionality of each function can…
Wei
  • 273
  • 1
  • 2
  • 8
10
votes
4 answers

SQL Number - Row_Number() - Allow Repeating Row Number

I'm using SQL Server 2008. I have this data returned in a query that looks pretty much like this ordered by Day and ManualOrder... ID Day ManualOrder Lat Lon 1 Mon 0 36.55 36.55 5 Mon 1 55.55 54.44 …
Andy
  • 1,243
  • 3
  • 22
  • 40
8
votes
2 answers

DENSE_RANK according to particular order

Hi I have a table of data I want to output the dense_rank of the names starting from the first group of names according to sorted dates order. e.g. DROP TABLE MyTable SELECT * INTO MyTable FROM ( VALUES ('2015-12-23', 'ccc'),('2015-12-21',…
user1589188
  • 5,316
  • 17
  • 67
  • 130
7
votes
3 answers

Dense Rank with order by

I have Assignment Table like this EMPLID | RCD | COMPANY | EFFDT | SALARY --------------------------------------------------- 100 | 0 | xyz | 1/1/2000 | 1000 100 | 0 | xyz | 1/15/2000 | 1100 100 | 0 |…
Bhushan
  • 115
  • 7
6
votes
3 answers

implement dense rank with linq

Using the following linq code, how can I add dense_rank to my results? If that's too slow or complicated, how about just the rank window function? var x = tableQueryable .Where(where condition) .GroupBy(cust=> new { fieldOne = cust.fieldOne…
Mike Turner
  • 471
  • 1
  • 7
  • 22
6
votes
1 answer

Row_Number() partitioning according to consecutive rows

I am working on a query for SQL Server 2008 that needs partition in a way that it considers the consecutive nature of the rows in the table, meaning that it has no "memory" and restart the row numbering when consecutiveness breaks down for a…
sinandrei
  • 179
  • 2
  • 10
6
votes
2 answers

Convert keep dense_rank from Oracle query into postgres

I'm trying to convert the following Oracle query into Postgres select this_.GLOBAL_TRANSACTION_ID as y0_, this_.BUSINESS_IDENTIFIER as y1_, this_.ENVIRONMENT as y2_, count(*) as y3_, this_.HOST_NAME as y4_, …
jvaello
  • 63
  • 1
  • 5
6
votes
1 answer

select records for range comparison

I am suck in this one. Wish I could do it in pure sql, but at this point any solution will do. I have ta and tb tables, containing lists of events that occurred approximately at the same time. The goal is to find "orphan" records from ta on tb.…
filippo
  • 5,583
  • 13
  • 50
  • 72
5
votes
1 answer

Ranking table on one column whilst sorting on another

I have a subset of a SQL Server 2008 R2 table like this: cust_id | prod_id | day | price --------+---------+-----+------- 137656 194528 42373 9.11 137656 194528 42374 9.11 137656 194528 42375 9.61 137656 194528 42376 …
samil90
  • 107
  • 6
5
votes
4 answers

DENSE_RANK() without duplication

Here's what my data looks like: | col1 | col2 | denserank | whatiwant | |------|------|-----------|-----------| | 1 | 1 | 1 | 1 | | 2 | 1 | 1 | 1 | | 3 | 2 | 2 | 2 | | 4 | 2 | 2…
Mansfield
  • 14,445
  • 18
  • 76
  • 112
5
votes
1 answer

ORDER BY in SQL Server vs Postgresql

I am trying to run ORDER BY on a huge data set and produce dense rank values to extract the distinct number of rows based on the dense rank.Later, I am using the dense rank value as a surrogate key in my entire process to carry forward the…
Teja
  • 13,214
  • 36
  • 93
  • 155
5
votes
2 answers

Exclude null values using DENSE_RANK

Dense_Rank is taking everything into account. Is there a way to exclude the null values so the next rank after 1 would be 2 and not 3. This is what the table looks like now: A | DENSE_R -------------- 1 | 1 -------------- 2 |…
ThatRiddimGuy
  • 381
  • 2
  • 6
  • 19
5
votes
2 answers

How to Dense Rank Sets of data

I am trying to get a dense rank to group sets of data together. In my table I have ID, GRP_SET, SUB_SET, and INTERVAL which simply represents a date field. When records are inserted using an ID they get inserted as GRP_SETs of 3 rows shown as a…
Cullen Burton
  • 53
  • 2
4
votes
2 answers

Snowflake - Dense_rank starting at 2 rather than 1

I'm using the following statement in a query: CASE WHEN apercent IS NULL THEN NULL ELSE dense_rank() over (partition by adate order by apercent desc) END as arank For some reason the ranked results are starting at 2 rather than 1. There are no…
tennis_fan89
  • 115
  • 7
4
votes
2 answers

Find second highest salary in each department using rank/dense_rank in hive

These were the two questions asked to me during an interview but only condition is to use rank/dense_rank. Find second highest salary in each department using rank/dense_rank in hive. When there are sufficient records in each department. When…
Programmeur
  • 190
  • 1
  • 14
1
2 3
…
23 24