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
-2
votes
3 answers

Rank function gives incorrect Ranking

select distinct a.pattern ,convert(numeric(18,0) ,isnull(b.[DCount]as [DCount] ,DENSE_RANK () OVER ( Partition by a.pattern order by b.[DCount]desc ) as [Rank] from a, b Gives output as Pattern Dcount Rank A 0 1 A 0 1 A 0 2 A 0 …
Karl
  • 41
  • 1
  • 7
-2
votes
1 answer

T-SQL - Dense_Rank returning strange numbers

The following Dense_Rank query returns very strange output: SELECT DENSE_RANK() OVER(PARTITION BY ColC ORDER BY ColB) AS [Rank] FROM ( SELECT *, concat(ColA, CONVERT(DATE, ColB)) AS ColC FROM ##Table ) Subquery1; Returns: ColB …
Statsanalyst
  • 331
  • 2
  • 3
  • 16
-3
votes
1 answer

How to use SQL to count events in the first week

I'm trying to write a SQL query, which says how many logins each user made in their first week. Assume, for the purpose of this question, that I have a table with at least user_id and login_date. I'm trying to produce an output table with user_id…
Diogenes Creosote
  • 1,922
  • 2
  • 17
  • 22
-5
votes
2 answers

SQL Server equivalent of dense_Rank() and TO_DATE() of Postgres

I have the below query coming from the Postgres database. I want to convert the below query from Postgres to an Azure SQL Server version. I know that TO_DATE can be written as convert(DATETIME,...) but I want to protect the date format too. Even…
yed2393
  • 262
  • 1
  • 12
-7
votes
1 answer

Find how many hat-trick 4's were scored by the batsmen from the following table in SQL

CREATE TABLE cricket_scores ( Ball_No INT,Batsman varchar(30) NOT NULL, Bowler Varchar(30) NOT NULL, Runs INT, PRIMARY KEY(Ball_No) ); INSERT INTO cricket_scores (ball_no,batsman,bowler,runs) VALUES ( 101 , "Kohli" , "Anderson" , 1 ) …
Sasidar B R
  • 11
  • 3
1 2 3
…
23
24