5 Replies Latest reply on Feb 17, 2009 2:30 PM by leland

    Need help with select distinct with group by

    leland
      RDBMS 10gr2

      I am trying achieve having a select distinct with a order by and after looking on the internet and trying different examples, I have been unsucessful.

      Here is the code working (not sorting - I wish to sort by pps.last_name however I can't seem to get it to work).
      select distinct pps.last_name || ', ' ||pps.first_name || ' ' ||pps.middle_initial || '.' d, 
             emple_no r
        from cobr.vw_pps_payroll pps,
             projman pm
       where term_date is null
         and department = '0004400000'
         and pm.eid != pps.emple_no
        • 1. Re: Need help with select distinct with group by
          469753
          Since the last name is the leading part or your concatenated column 1, how about this:
          select distinct pps.last_name || ', ' ||pps.first_name || ' ' ||pps.middle_initial || '.' d, 
                 emple_no r
            from cobr.vw_pps_payroll pps,
                 projman pm
           where term_date is null
             and department = '0004400000'
             and pm.eid != pps.emple_no
          order by 1
          • 2. Re: Need help with select distinct with group by
            666352
            SQL> SELECT   ename || '-' || empno, sal
              2      FROM emp_test
              3  ORDER BY ename;
            
            ENAME||'-'||EMPNO                                                                         SAL
            ---------------------------------------------------------------------------------- ----------
            BLAKE-7698                                                                              20000
            CLARK-7782                                                                              20000
            DAVID-7699                                                                              20000
            FORD-7902                                                                                6000
            JONES-7566                                                                               5950
            KING-7839                                                                               10000
            SCOTT-7788                                                                               6000
            
            7 ligne(s) sélectionnée(s).
            
            SQL> 
            • 3. Re: Need help with select distinct with group by
              667579
              He needs a distinct.

              Here's another solution:
              select pps.last_name || ', ' ||pps.first_name || ' ' ||pps.middle_initial || '.' d, 
                     emple_no r
                from (select distinct last_name, first_name, middle_initial from cobr.vw_pps_payroll) pps,
                     projman pm
               where term_date is null
                 and department = '0004400000'
                 and pm.eid != pps.emple_no
              order by pps.last_name;
              1 person found this helpful
              • 4. Re: Need help with select distinct with group by
                Aketi Jyuuzou
                perhaps we can use below solution :D
                select d,r
                from (select pps.last_name || ', ' ||pps.first_name || ' ' ||pps.middle_initial || '.' d, 
                             emple_no r,last_name
                        from cobr.vw_pps_payroll pps,
                             projman pm
                       where term_date is null
                         and department = '0004400000'
                         and pm.eid != pps.emple_no)
                group by d,r
                order by min(last_name);
                1 person found this helpful
                • 5. Re: Need help with select distinct with group by
                  leland
                  I never thought of that, in fact I've always used the column name itself, instead of using 1.
                  Thanks!

                  I tested TK's and Aketi and they also worked so I appreciate the input.