Как использовать RANK() в SQL Server

У меня проблема с использованием RANK() в SQL Server.

Вот мой код:

SELECT contendernum,
       totals, 
       RANK() OVER (PARTITION BY ContenderNum ORDER BY totals ASC) AS xRank
FROM (
   SELECT ContenderNum,
          SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
   FROM Cat1GroupImpersonation
   GROUP BY ContenderNum
) AS a

Результаты для этого запроса:

contendernum    totals    xRank
          1       196        1
          2       181        1
          3       192        1
          4       181        1
          5       179        1

Какой мой желаемый результат:

contendernum    totals    xRank
          1       196        1
          2       181        3
          3       192        2
          4       181        3
          5       179        4

Я хочу ранжировать результат на основе totals. Если есть такое же значение, как 181, то два числа будут иметь тот же xRank.

Ответ 1

Просто измените

OVER (PARTITION BY ContenderNum ORDER BY totals ASC)

OVER (ORDER BY totals ASC)

Взгляните на этот пример

SQL Fiddle DEMO

Вы также можете посмотреть разницу между RANK (Transact-SQL) и DENSE_RANK (Transact- SQL)

RANK (Transact-SQL)

Если две или несколько строк привязываются к рангу, каждая связанная строка получает одинаковые ранг. Например, если у двух топ-менеджеров есть тот же SalesYTD ценность, они оба заняли одно место. Продавца со следующим самым высоким SalesYTD занимает третье место, потому что есть две строки, которые оценивается выше. Поэтому функция RANK не всегда возвращает последовательные целые числа.

DENSE_RANK (Transact-SQL)

Возвращает ранг строк в разделе набора результатов без любые пробелы в рейтинге. Ранг строки - это плюс плюс число отличные ранги, которые выходят перед рассматриваемой строкой.

Ответ 2

Чтобы ответить на заголовок вопроса, "Как использовать Rank() в SQL Server", вот как это работает:

Я буду использовать этот набор данных в качестве примера:

create table #tmp
(
  column1 varchar(3),
  column2 varchar(5),
  column3 datetime,
  column4 int
)

insert into #tmp values ('AAA', 'SKA', '2013-02-01 00:00:00', 10)
insert into #tmp values ('AAA', 'SKA', '2013-01-31 00:00:00', 15)
insert into #tmp values ('AAA', 'SKB', '2013-01-31 00:00:00', 20)
insert into #tmp values ('AAA', 'SKB', '2013-01-15 00:00:00', 5)
insert into #tmp values ('AAA', 'SKC', '2013-02-01 00:00:00', 25)

У вас есть раздел, который в основном определяет группировку.

В этом примере, если вы разбиваете на столбец2, функция ранга будет создавать ранги для групп значений столбца2. Там будут разные ранги для строк, где column2 = "SKA", чем строки, где column2 = "SKB" и т.д.

Ранги решаются следующим образом: Ранг для каждой записи - это плюс плюс число рангов, которые находятся перед ним в своем разделе. Ранг будет увеличиваться только тогда, когда одно из выбранных вами полей (кроме полей (секций)) отличается от тех, которые были перед ним. Если все выбранные поля совпадают, а ранги будут привязаны, и им будет присвоено значение, одно.

Зная это, если мы хотим только выбрать одно значение из каждой группы в столбце два, мы могли бы использовать этот запрос:

with cte as 
(
  select *, 
  rank() over (partition by column2 
             order by column3) rnk
  from t

) select * from cte where rnk = 1 order by column3;

Результат:

COLUMN1 | COLUMN2   | COLUMN3                           |COLUMN4 | RNK
------------------------------------------------------------------------------
AAA     | SKB   | January, 15 2013 00:00:00+0000    |5   | 1
AAA     | SKA   | January, 31 2013 00:00:00+0000    |15  | 1
AAA     | SKC   | February, 01 2013 00:00:00+0000   |25  | 1

SQL DEMO

Ответ 3

Вам нужно использовать DENSE_RANK, а не RANK. Единственное различие заключается в том, что он не оставляет пробелов. Вы также не должны разбивать на contender_num, иначе вы ставите каждого соперника в отдельной группе, поэтому каждый из них занимает первое место в своих разделенных группах!

SELECT contendernum,totals, DENSE_RANK() OVER (ORDER BY totals desc) AS xRank FROM
(
SELECT ContenderNum ,SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
FROM dbo.Cat1GroupImpersonation
 GROUP BY ContenderNum
) AS a
order by contendernum

Подсказка для использования StackOverflow, пожалуйста, напишите DDL и образцы данных, чтобы люди могли помочь вам использовать меньше своего времени!

create table Cat1GroupImpersonation (
contendernum int,
criteria1 int,
criteria2 int,
criteria3 int,
criteria4 int);

insert Cat1GroupImpersonation select
1,196,0,0,0 union all select
2,181,0,0,0 union all select
3,192,0,0,0 union all select
4,181,0,0,0 union all select
5,179,0,0,0;

Ответ 4

DENSE_RANK() - это ранг без пробелов, т.е. он "плотный".

select Name,EmailId,salary,DENSE_RANK() over(order by salary asc) from [dbo].[Employees]

RANK() - содержит пробел между рангами.

select Name,EmailId,salary,RANK() over(order by salary asc) from [dbo].[Employees]

Ответ 5

Вы уже сгруппированы ContenderNum, не нужно снова разделять его. Используйте Dense_rank() и порядок по итоговым значениям. Короче говоря,

SELECT contendernum,totals, **DENSE_RANK()** 
OVER (ORDER BY totals **DESC**) 
AS xRank 
FROM
(
   SELECT ContenderNum ,SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
   FROM dbo.Cat1GroupImpersonation
   GROUP BY ContenderNum
) AS a

Ответ 6

SELECT contendernum,totals, RANK() OVER (ORDER BY totals ASC) AS xRank FROM
(
SELECT ContenderNum ,SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
FROM dbo.Cat1GroupImpersonation
 GROUP BY ContenderNum
) AS a