Как вернуть строку с наибольшим количеством строк?

Question

Мой SQL возвращает список идентификаторов пользователей с их ролями, смысл состоит в том, чтобы объединить все роли (группы) в одну строку. В настоящее время он возвращает все, но мне просто нужно вернуть одну запись на пользователя, в основном запись на пользователя с наибольшим значением rn (номер строки) для этого пользователя.

SELECT 
  IT_ID, 
  SUBSTR (SYS_CONNECT_BY_PATH (grp , ','), 2) GROUPS
FROM (
  SELECT 
    U.IT_ID, 
    LAST_NAME, 
    BFIRST_NAME, 
    GRP, 
    ROW_NUMBER() OVER (partition by u.it_id ORDER BY U.IT_ID) rn, 
    COUNT(*) OVER() cnt
FROM ECG_IT_USERS U
JOIN SECUREGROUPS G ON U.IT_ID = G.IT_ID)
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1 and it_id = prior it_id
Group by it_id

Возвращает:

IT_ID   GROUPS
afz23   ADMIN
afz23   ADMIN, QA
klf44   USER
klf44   USER, BUSINESS

Мне нужно вернуться

IT_ID   GROUPS
afz23   ADMIN, QA
klf44   USER, BUSINESS

Answer 1

Это самая большая проблема для каждой группы. Здесь можно найти исчерпывающий ответ: SQL Выберите только строки с максимальным значением для столбца.

Поскольку время сейчас ограничено, я не могу работать над вашим запросом. Не давать вам рыбу, а учить ловить рыбу.

Answer 2

select it_id, groups
from 
(
    select 
      it_id
      , groups
      , row_number () over (partition by id_id order by length(groups) desc ) rn
    from 
      (
          SELECT 
            IT_ID, 
            SUBSTR (SYS_CONNECT_BY_PATH (grp , ','), 2) GROUPS
          FROM (
            SELECT 
              U.IT_ID, 
              LAST_NAME, 
              BFIRST_NAME, 
              GRP, 
              ROW_NUMBER() OVER (partition by u.it_id ORDER BY U.IT_ID) rn, 
              COUNT(*) OVER() cnt
          FROM ECG_IT_USERS U
          JOIN SECUREGROUPS G ON U.IT_ID = G.IT_ID)
          START WITH rn = 1
          CONNECT BY rn = PRIOR rn + 1 and it_id = prior it_id
          Group by it_id
      )
 )
where rn = 1
;

Answer 3

Добавьте еще одну аналитическую функцию, которая вычисляет максимум числа строк по каждому IT_ID раздел. Затем выберите строку, где rn = max_rn.