5 Replies Latest reply: Jan 10, 2013 11:24 PM by user503635 RSS

    Turn query into a GROUP BY

    user13005731
      I'm trying to turn the query below so it only produces the first line for each user,each responsibility combination:

      select a.LOGIN_ID,a.START_TIME,a.END_TIME,c.RESPONSIBILITY_NAME,b.USER_ID,d.USER_NAME from FND_LOGIN_RESPONSIBILITIES a,
      fnd_logins b,
      FND_RESPONSIBILITY_tl c,
      fnd_user d
      where
      a.LOGIN_ID=b.LOGIN_ID
      and a.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
      and b.USER_ID=d.USER_ID
      order by d.user_name, c.responsibility_name, a.end_TIME desc nulls last
      That's for a unique combination of user and responsibility, it should return only the first value. Currently it's returning multiple outputs int the format:
       LOGIN_ID START_TIM END_TIME  RESPONSIBILITY_NAME                                        USER_ID USER_NAME
      ---------- --------- --------- ------------------------------------------------------- ---------- -------------------------------------------------------
         1487207 23-NOV-11 23-NOV-11 XXX  Payables Supervisor                                1236 ZZZ
         1487207 23-NOV-11 23-NOV-11 XXX  Payables Supervisor                                1236 ZZZ
         1480785 22-NOV-11 22-NOV-11 XXX  Payables Supervisor                                1236 ZZZ
         1478246 22-NOV-11 22-NOV-11 XXX  Payables Supervisor                                1236 ZZZ
         1478246 22-NOV-11 22-NOV-11 XXX  Payables Supervisor                                1236 ZZZ
         3149187 14-DEC-12 14-DEC-12 XXX Imprest Employee Responsibility                           5825 
         3136830 11-DEC-12 11-DEC-12 XXX Imprest Employee Responsibility                           5825 ZZZ
         3102226 03-DEC-12 03-DEC-12 XXX Imprest Employee Responsibility                           5825 ZZZ
         3094387 30-NOV-12 30-NOV-12 XXX Imprest Employee Responsibility                           5825 ZZZ
         3094387 30-NOV-12 30-NOV-12 XXX Imprest Employee Responsibility                           5825 ZZZ
         3093463 29-NOV-12 29-NOV-12 XXX Imprest Employee Responsibility                           5825 ZZZ
         3090327 29-NOV-12 29-NOV-12 XXX Imprest Employee Responsibility                           5825 ZZZ
      This should only output as below:
       LOGIN_ID START_TIM END_TIME  RESPONSIBILITY_NAME                              USER_ID USER_NAME
      ---------- --------- --------- ------------------------------------------------------- ---------- -------------------------------------------------------
       1487207 23-NOV-11 23-NOV-11 XXX  Payables Supervisor                                1236       ZZZ
       3149187 14-DEC-12 14-DEC-12 XXX Imprest Employee Responsibility                   5825      ZZZ
      My database version is 11.2.0.2.0

      James
        • 1. Re: Turn query into a GROUP BY
          jeneesh
          One way
          select *
          from
          (
          select      a.LOGIN_ID,a.START_TIME,a.END_TIME,
               c.RESPONSIBILITY_NAME,b.USER_ID,d.USER_NAME,
               row_number() over(partition by d.user_name, c.responsibility_name
                               order by a.end_TIME desc nulls last ) rn
          from      FND_LOGIN_RESPONSIBILITIES a,
               fnd_logins b,
               FND_RESPONSIBILITY_tl c,
               fnd_user d
          where a.LOGIN_ID=b.LOGIN_ID
          and a.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
          and b.USER_ID=d.USER_ID
          )
          where rn = 1
          order by user_name, responsibility_name
          • 2. Re: Turn query into a GROUP BY
            user13005731
            Many thanks jeneesh      , that helped a lot!
            • 3. Re: Turn query into a GROUP BY
              jeneesh
              Or if you want to use GROUP BY
              select      max(a.LOGIN_ID) 
                      keep(dense_rank first order by a.end_TIME desc nulls last) login_id,
                   max(a.START_TIME) 
                      keep(dense_rank first order by a.end_TIME desc nulls last) START_TIME
                   max(a.LOGIN_ID) 
                      keep(dense_rank first order by a.end_TIME desc nulls last) login_id
                   max(a.END_TIME) END_TIME,
                   c.RESPONSIBILITY_NAME,b.USER_ID,d.USER_NAME
              from      FND_LOGIN_RESPONSIBILITIES a,
                   fnd_logins b,
                   FND_RESPONSIBILITY_tl c,
                   fnd_user d
              where a.LOGIN_ID=b.LOGIN_ID
              and a.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
              and b.USER_ID=d.USER_ID
              group by c.RESPONSIBILITY_NAME,b.USER_ID,d.USER_NAME
              order by d.user_name, c.responsibility_name
              • 4. Re: Turn query into a GROUP BY
                user503635
                Try to understand and guess your entity relationship with limited info in hand, you may try below SQL
                Select a1.login_id 
                      a1.START_TIME,
                      a1.END_TIME,
                      c.RESPONSIBILITY_NAME,
                      b.USER_ID,
                      d.USER_NAME 
                (select a.LOGIN_ID,
                        a.RESPONSIBILITY_ID
                       b.user_id, 
                from  FND_LOGIN_RESPONSIBILITIES a,
                      fnd_logins b,
                where a.LOGIN_ID=b.LOGIN_ID
                 group by a.login_id, b.user_id) t
                      FND_LOGIN_RESPONSIBILITIES a1,
                      FND_RESPONSIBILITY_tl c,
                      fnd_user d
                 where a1.lonin_id = t.login_id
                   and a1.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
                   and t.USER_ID= d.USER_ID
                However not sure you data attributes and content, cannot run in my database.

                Edited by: user503635 on Jan 10, 2013 9:21 PM
                • 5. Re: Turn query into a GROUP BY
                  user503635
                  amended
                  Select a1.login_id 
                        a1.START_TIME,
                        a1.END_TIME,
                        c.RESPONSIBILITY_NAME,
                        b.USER_ID,
                        d.USER_NAME 
                  (select  b.user_id, 
                            a.RESPONSIBILITY_ID,
                            min(a.LOGIN_ID)
                  from  FND_LOGIN_RESPONSIBILITIES a,
                        fnd_logins b,
                  where a.LOGIN_ID=b.LOGIN_ID
                   group by b.user_id,  a.RESPONSIBILITY_ID) t
                        FND_LOGIN_RESPONSIBILITIES a1,
                        FND_RESPONSIBILITY_tl c,
                        fnd_user d
                   where a1.lonin_id = t.login_id
                     and a1.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
                     and t.USER_ID= d.USER_ID