2 Replies Latest reply: Feb 20, 2013 4:43 PM by Sg049 RSS

    sql squery for one to many relationship

    918769
      I need to write sql for below requirement:

      table structure is

      serial no LPN

      1 4
      2 4
      3 6
      4 6
      5 6
      6 3
      7 3
      8 3
      9 1

      I have to pick distinct 'LPN' like below:
      (any serial no can be picked for the distinct LPN)

      results needs to be as below:

      serial no LPN
      1 4
      3 6
      6 3
      9 1

      Please suggest with sql.
        • 1. Re: sql squery for one to many relationship
          Frank Kulash
          Hi,
          915766 wrote:
          I need to write sql for below requirement:

          table structure is

          serial no LPN

          1 4
          2 4
          3 6
          4 6
          5 6
          6 3
          7 3
          8 3
          9 1

          I have to pick distinct 'LPN' like below:
          That sounds like a job for "GROUP BY lpn".
          (any serial no can be picked for the distinct LPN)
          It looks like you're displaying the lowest serial_no for each lpn. That's easy to do, using the aggregate MIN function.
          results needs to be as below:

          serial no LPN
          1 4
          3 6
          6 3
          9 1

          Please suggest with sql.
          Here's one way:
          SELECT    MIN (serial_no)   AS serial_no
          ,         lpn
          FROM      table_x
          GROUP BY  lpn
          ORDER BY  lpn     -- if wanted
          ;
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: sql squery for one to many relationship
            Sg049
            Use Analytic function to get the o/p and do partition by lpn as shown below
            with t(serialno,lpn)
            as
            (select 1, 4 from dual union
            select 2, 4 from dual union
            select 3, 6 from dual union
            select 4, 6 from dual union
            select 5, 6 from dual union
            select 6, 3 from dual union
            select 7, 3 from dual union
            select 8, 3 from dual union
            select 9, 1 from dual)
            select serialno,lpn from (select t.*,row_number() over(partition by lpn order by serialno asc) rn from t)
            where rn=1
            order by serialno;
            
            serialno  lpn   
            --------------
            1       4
            3       6
            6       3
            9       1