Как выбрать первую строку в каждой группе с сортировкой по столбцам

Покажу на примере, как выбрать первую строку в каждой группе логинов, отсортированных по столбцам IsActive и Created.

Есть таблица:

Id | Login | IsActive | Created 1 | test1 | 1 | 2016-01-01 2 | test1 | 0 | 2015-05-21 3 | test2 | 0 | 2016-07-03 4 | test2 | 0 | 2017-01-22

Нужна одна строка на логин — та, где логин активен. Если активной строки нет, беру созданную последней. Здесь это строки 1 и 4.

Задачу решает такой скрипт:

with cte as (
  select *,
  rank() over (partition by [Login] order by [Active] desc, Created desc) as [r]
  from [User]
)
SELECT * FROM cte where [r] = 1

rank() over сортирует строки в нужном порядке и записывает номер в столбец [r]. Затем я беру первую строку каждой группы: SELECT * FROM cte where [r] = 1

Чтобы использовать это в join, поставьте блок "with" сверху:

with cte as (
  select *,
  rank() over (partition by [Login] order by [Active] desc, Created desc) as [r]
  from [User]
)
SELECT * FROM Employees emp
LEFT JOIN (SELECT * FROM cte where [r] = 1) u on emp.[Login]=u.[Login]