4 Replies Latest reply: Aug 26, 2013 5:57 AM by Ishan RSS

    list only one row for related records

    Tshifhiwa

      hi i have outer join query,but my problem is i what to display only one record for related recordS,e.g if i have two user related to other table i only one to list the first record

       

       

      SELECT DISTINCT UamUserdetails.USERNAME,

              UamUserdetails.FIRSTNAME,

             UamUserdetails.SURNAME,

             USR.ORGANISATIONID,

             USR.ASSOCSTATCODE

      FROM UAM_USERDETAILS UamUserdetails,uam_organisation_user usr

      where UAMUSERDETAILS.USERNAME = USR.USERNAME(+)

      --AND  UAMUSERDETAILS.STATUSCODE = 'A'

      AND ASSOCSTATCODE NOT IN('NEW','ADMNP','PENDG')

        • 1. Re: list only one row for related records
          Soofi

          Hi,

           

          Use this Query

          select * from table_name where rowid in(

          select min(rowid) a from table_name

          group by column_name);

           

          Replace the table_name and Column_name of yours and check.

           

          Any Issues let me know...

           

          Regards,

          Soofi

          • 2. Re: list only one row for related records
            Frank Kulash

            Hi,

             

            When there is are 2 or more rows in a group, which one do you want to show?

            If you don't care, then you can use a solution based on ROWID, otherwise not.  For example, if you want to show exactly one row from scott.emp for each job, and you want that to be the one with the ealiest hiredate (or one of the rows with  the earliest hiredate, in case of a tie), then you can use the analytic ROW_NUMBER function like this:

             

            WITH  got_r_num  AS

            (

                SELECT  *        -- Or list whatever columns you want

                ,       ROW_NUMBER () OVER ( PARTITION BY  job

                                             ORDER BY      hiredate

                                           )  AS r_num

                FROM    scott.emp

            --  WHERE ...       -- If you need any filtering put it here

            )

            SELECT    *         -- Or list all columns except r_num

            FROM      got_r_num

            WHERE     r_num     = 1

            ;

            • 3. Re: list only one row for related records
              Tshifhiwa

              i what where status code='A'

               

              SELECT * FROM UAM_USERDETAILS where rowid in(

              select min(rowid)a from  UAM_USERDETAILS

              group by  STATUSCODE)

              where STATUSCODE = 'A'

               

              i what to display between two tables

              SELECT DISTINCT UamUserdetails.USERNAME,

                      UamUserdetails.FIRSTNAME,

                     UamUserdetails.SURNAME,

                     USR.ORGANISATIONID,

                     USR.ASSOCSTATCODE

              FROM UAM_USERDETAILS UamUserdetails,uam_organisation_user usr

              where UAMUSERDETAILS.USERNAME = USR.USERNAME(+)

              --AND  UAMUSERDETAILS.STATUSCODE = 'A'

              AND ASSOCSTATCODE NOT IN('NEW','ADMNP','PENDG')

               

              i what to display all rows in this table UAM_USERDETAILS who got status A

               

              but if there is more than one row related to uam_organisation_user  i what to displau only the first row

               

              the record is like this

              USERNAMEFIRSTNAMESURNAMEORGANISATIONIDASSOCSTATCODE
              lmoloileratomoloi3 915ACCEP
              SSULEMANtestwwww4 697ADMIN
              ZXOBOzandilexobo5 135ADMIN
              MMABUZAMochecheMabuza5 143ADMIN
              RD0001shonisanitshisikule5 173ADMIN
              RD0001shonisanitshisikule5 165ADMIN

              i what it to display like this

              USERNAMEFIRSTNAMESURNAMEORGANISATIONIDASSOCSTATCODE
              lmoloileratomoloi3 915ACCEP
              SSULEMANtestwwww4 697ADMIN
              ZXOBOzandilexobo5 135ADMIN
              MMABUZAMochecheMabuza5 143ADMIN
              RD0001shonisanitshisikule5 173ADMIN
              • 4. Re: list only one row for related records
                Ishan

                but if there is more than one row related to uam_organisation_user  i what to displau only the first row

                There is nothing called First Row. The row order that you see in the table is not necessarily the same as stored in database. You have to use ORDER BY clause to determine which is the first row. In your example, for USERNAME "RD0001" there are two entries. You can use Frank's solution, order by on ORGANISATIONID and then filter it out by using r_num =1.

                 

                Ishan