How to return the row with the largest row count?

Question

My SQL returns a list of user ID's with their roles, the point is to concatenate all roles (groups) into one line. Currently, it returns everything, but I just need to return one entry per user, basically the entry per user with the largest rn (row number) for that user.

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 returns:

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

I need to return

IT_ID   GROUPS
afz23   ADMIN, QA
klf44   USER, BUSINESS

score 0 · Answer 1 · answered Nov 07 '11 at 14:27

0

Add another analytic function that calculates the maximum of the row number over each IT_ID partition. Then select the row where rn = max_rn.

answered Nov 07 '11 at 14:27

tobiasbayer

10,269
4
46
64

score 0 · Answer 2 · answered Nov 07 '11 at 14:31

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
;

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

This is a greatest-n-per-group problem. There's a very comprehensive answer on the topic here: SQL Select only rows with Max Value on a Column

Since time is now limited, I cannot work on your query. Not giving you the fish, but teaching how to fish.

edited May 23 '17 at 12:03

Community

1
1

answered Nov 07 '11 at 14:35

Adriano Carneiro

57,693
12
90
123

How to return the row with the largest row count?

3 Answers3