Loading ...
Sorry, an error occurred while loading the content.

Query Required - Urgent

Expand Messages
  • Kamran Khan
    I have the following table in Oracle 9i: ID E.No. ===================== 1 150 2 150 3 150 4 120 5 120 6 120 7 120 8 110 9 110 10 100 11 100
    Message 1 of 10 , Mar 2, 2005
    • 0 Attachment
      I have the following table in Oracle 9i:
      ID  E.No.
      =====================
      1   150
      2   150
      3   150
      4   120
      5   120
      6   120
      7   120
      8   110
      9   110
      10 100
      11 100
      12 100
      13 150
      14 150
      15 150
      And i want to create the following result from the above table but
      I am unable to write query to get the same result. However I have
      written the pl/sql program to get the result. Is there anybody
      who can help me to write down qry to get the following result.
       
      Result:
       
      From ID      To ID      E.No.    Rec. Count
      ==============================================================
      1                   3         150            3
      4                   7         120            4
      8                   9         110            2
      10               12          150            3
       
      Thanking you in advance.
       
      Kamran Khan

      Send instant messages to your online friends http://uk.messenger.yahoo.com

    • Faisal Ijaz
      Kamarn, Here is the query that you want: select * from TABLE_NAME where ID in (select min(ID) from table_name group by E_NO); I have verified the results for
      Message 2 of 10 , Mar 2, 2005
      • 0 Attachment
        Kamarn,
         
        Here is the query that you want:
         
        select * from TABLE_NAME where ID in (select min(ID) from table_name group by E_NO);
         
        I have verified the results for your query.
         
        Best Regards
         
        Faisal Ijaz
         
         
         
         


        Kamran Khan <kamran_orcl@...> wrote:
        I have the following table in Oracle 9i:
        ID  E.No.
        =====================
        1   150
        2   150
        3   150
        4   120
        5   120
        6   120
        7   120
        8   110
        9   110
        10 100
        11 100
        12 100
        13 150
        14 150
        15 150
        And i want to create the following result from the above table but
        I am unable to write query to get the same result. However I have
        written the pl/sql program to get the result. Is there anybody
        who can help me to write down qry to get the following result.
         
        Result:
         
        From ID      To ID      E.No.    Rec. Count
        ==============================================================
        1                   3         150            3
        4                   7         120            4
        8                   9         110            2
        10               12          150            3
         
        Thanking you in advance.
         
        Kamran Khan

        Send instant messages to your online friends http://uk.messenger.yahoo.com

        Offical Website http://www.pkoug.org
        Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
           dba@...
           ids@...
           ias@...
           java@...
           ocs@...
           apps@...
           linux@...

        Pakistan Oracle Users Group




        Celebrate Yahoo!'s 10th Birthday!
        Yahoo! Netrospective: 100 Moments of the Web

      • Kamran Khan
        Dear Faisal, Sorry, your query does not fullfill my requirement, because you have grouped the result on E_No in subquery whereas there are repaeting E_No in
        Message 3 of 10 , Mar 2, 2005
        • 0 Attachment
          Dear Faisal,

          Sorry, your query does not fullfill my requirement, because you have
          grouped the result on E_No in subquery whereas there are repaeting
          E_No in the table.

          --- In PkOug@yahoogroups.com, Faisal Ijaz <faisal_ijaz2000@y...>
          wrote:
          > Kamarn,
          >
          > Here is the query that you want:
          >
          > select * from TABLE_NAME where ID in (select min(ID) from
          table_name group by E_NO);
          >
          > I have verified the results for your query.
          >
          > Best Regards
          >
          > Faisal Ijaz
          >
          >
          >
          >
          >
          >
          > Kamran Khan <kamran_orcl@y...> wrote:
          > I have the following table in Oracle 9i:
          > ID E.No.
          > =====================
          > 1 150
          > 2 150
          > 3 150
          > 4 120
          > 5 120
          > 6 120
          > 7 120
          > 8 110
          > 9 110
          > 10 100
          > 11 100
          > 12 100
          > 13 150
          > 14 150
          > 15 150
          > And i want to create the following result from the above table but
          > I am unable to write query to get the same result. However I have
          > written the pl/sql program to get the result. Is there anybody
          > who can help me to write down qry to get the following result.
          >
          > Result:
          >
          > From ID To ID E.No. Rec. Count
          > ==============================================================
          > 1 3 150 3
          > 4 7 120 4
          > 8 9 110 2
          > 10 12 150 3
          >
          > Thanking you in advance.
          >
          > Kamran Khan
          >
          > Send instant messages to your online friends
          http://uk.messenger.yahoo.com
          >
          > Offical Website http://www.pkoug.org
          > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
          > dba@p...
          > ids@p...
          > ias@p...
          > java@p...
          > ocs@p...
          > apps@p...
          > linux@p...
          >
          > Pakistan Oracle Users Group
          >
          >
          > Yahoo! Groups SponsorADVERTISEMENT
          >
          >
          > ---------------------------------
          > Yahoo! Groups Links
          >
          > To visit your group on the web, go to:
          > http://groups.yahoo.com/group/PkOug/
          >
          > To unsubscribe from this group, send an email to:
          > PkOug-unsubscribe@yahoogroups.com
          >
          > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
          Service.
          >
          >
          >
          > ---------------------------------
          > Celebrate Yahoo!'s 10th Birthday!
          > Yahoo! Netrospective: 100 Moments of the Web
        • Muhammad Kawish Siddiqui
          AOA check following query for this select min(id) from_ID, max(id) TO_ID, eno, count(1) from test group by eno; Best Regards, Muhammad Kawish Siddiqui
          Message 4 of 10 , Mar 3, 2005
          • 0 Attachment
            AOA
            check following query for this
             
            select min(id) from_ID, max(id) TO_ID, eno, count(1) from test group by eno;
             
             


            Best Regards,

            Muhammad Kawish Siddiqui
            Sr.Production Support / Software Engineer
            DWH Division
            DPS.(www.d-p-s.com)
            Islamabad.
            ----- Original Message -----
            Sent: Wednesday, March 02, 2005 6:16 PM
            Subject: Re: [PkOug] Query Required - Urgent (Your Required Query)

            Kamarn,
             
            Here is the query that you want:
             
            select * from TABLE_NAME where ID in (select min(ID) from table_name group by E_NO);
             
            I have verified the results for your query.
             
            Best Regards
             
            Faisal Ijaz
             
             
             
             


            Kamran Khan <kamran_orcl@...> wrote:
            I have the following table in Oracle 9i:
            ID  E.No.
            =====================
            1   150
            2   150
            3   150
            4   120
            5   120
            6   120
            7   120
            8   110
            9   110
            10 100
            11 100
            12 100
            13 150
            14 150
            15 150
            And i want to create the following result from the above table but
            I am unable to write query to get the same result. However I have
            written the pl/sql program to get the result. Is there anybody
            who can help me to write down qry to get the following result.
             
            Result:
             
            From ID      To ID      E.No.    Rec. Count
            ==============================================================
            1                   3         150            3
            4                   7         120            4
            8                   9         110            2
            10               12          150            3
             
            Thanking you in advance.
             
            Kamran Khan

            Send instant messages to your online friends http://uk.messenger.yahoo.com

            Offical Website http://www.pkoug.org
            Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
               dba@...
               ids@...
               ias@...
               java@...
               ocs@...
               apps@...
               linux@...

            Pakistan Oracle Users Group




            Celebrate Yahoo!'s 10th Birthday!
            Yahoo! Netrospective: 100 Moments of the Web

            Offical Website http://www.pkoug.org
            Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
               dba@...
               ids@...
               ias@...
               java@...
               ocs@...
               apps@...
               linux@...

            Pakistan Oracle Users Group



          • Faisal Ijaz
            This query is very simple but will not serve Mr. Kamrans purpose as it will not show the 150 group twice. Your query will group the 2 150 Group and treat it
            Message 5 of 10 , Mar 3, 2005
            • 0 Attachment
              This query is very simple but will not serve Mr. Kamrans purpose as it will not show the 150 group twice. Your query will group the 2 "150" Group and treat it as one and from the required output shown by Kamran it repeat twice.
               
              Regards
               
              Faisal. 

              Muhammad Kawish Siddiqui <kawish_siddiqui@...> wrote:
              AOA
              check following query for this
               
              select min(id) from_ID, max(id) TO_ID, eno, count(1) from test group by eno;
               
               


              Best Regards,

              Muhammad Kawish Siddiqui
              Sr.Production Support / Software Engineer
              DWH Division
              DPS.(www.d-p-s.com)
              Islamabad.
              ----- Original Message -----
              Sent: Wednesday, March 02, 2005 6:16 PM
              Subject: Re: [PkOug] Query Required - Urgent (Your Required Query)

              Kamarn,
               
              Here is the query that you want:
               
              select * from TABLE_NAME where ID in (select min(ID) from table_name group by E_NO);
               
              I have verified the results for your query.
               
              Best Regards
               
              Faisal Ijaz
               
               
               
               


              Kamran Khan <kamran_orcl@...> wrote:
              I have the following table in Oracle 9i:
              ID  E.No.
              =====================
              1   150
              2   150
              3   150
              4   120
              5   120
              6   120
              7   120
              8   110
              9   110
              10 100
              11 100
              12 100
              13 150
              14 150
              15 150
              And i want to create the following result from the above table but
              I am unable to write query to get the same result. However I have
              written the pl/sql program to get the result. Is there anybody
              who can help me to write down qry to get the following result.
               
              Result:
               
              From ID      To ID      E.No.    Rec. Count
              ==============================================================
              1                   3         150            3
              4                   7         120            4
              8                   9         110            2
              10               12          150            3
               
              Thanking you in advance.
               
              Kamran Khan

              Send instant messages to your online friends http://uk.messenger.yahoo.com

              Offical Website http://www.pkoug.org
              Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                 dba@...
                 ids@...
                 ias@...
                 java@...
                 ocs@...
                 apps@...
                 linux@...

              Pakistan Oracle Users Group




              Celebrate Yahoo!'s 10th Birthday!
              Yahoo! Netrospective: 100 Moments of the Web

              Offical Website http://www.pkoug.org
              Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                 dba@...
                 ids@...
                 ias@...
                 java@...
                 ocs@...
                 apps@...
                 linux@...

              Pakistan Oracle Users Group





              Offical Website http://www.pkoug.org
              Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                 dba@...
                 ids@...
                 ias@...
                 java@...
                 ocs@...
                 apps@...
                 linux@...

              Pakistan Oracle Users Group



              Yahoo! Groups Sponsor
              ADVERTISEMENT
              click here


              Celebrate Yahoo!'s 10th Birthday!
              Yahoo! Netrospective: 100 Moments of the Web

            • dmit@beaconhouse.edu.pk
              The sample data that Kamran has shared, has E.No 150 for last three IDs as well. Also he wants that these should be listed separately, as a different group.
              Message 6 of 10 , Mar 3, 2005
              • 0 Attachment
                The sample data that Kamran has shared, has E.No 150 for last three IDs as well.
                 
                Also he wants that these should be listed separately, as a different group.
                 
                Achieving it through SQL is probably not possible (as per my limited knowledge). 
                 
                The required output requires holding on the last E.No to be compared with the next record so as to have a break-point.
                 
                This I feel requires a PL/SQL block.
                 
                Aleem


                From: Muhammad Kawish Siddiqui [mailto:kawish_siddiqui@...]
                Sent: Thursday, March 03, 2005 1:50 PM
                To: PkOug@yahoogroups.com
                Subject: Re: [PkOug] Query Required - Urgent (Your Required Query)

                AOA
                check following query for this
                 
                select min(id) from_ID, max(id) TO_ID, eno, count(1) from test group by eno;
                 
                 


                Best Regards,

                Muhammad Kawish Siddiqui
                Sr.Production Support / Software Engineer
                DWH Division
                DPS.(www.d-p-s.com)
                Islamabad.
                ----- Original Message -----
                Sent: Wednesday, March 02, 2005 6:16 PM
                Subject: Re: [PkOug] Query Required - Urgent (Your Required Query)

                Kamarn,
                 
                Here is the query that you want:
                 
                select * from TABLE_NAME where ID in (select min(ID) from table_name group by E_NO);
                 
                I have verified the results for your query.
                 
                Best Regards
                 
                Faisal Ijaz
                 
                 
                 
                 


                Kamran Khan <kamran_orcl@...> wrote:
                I have the following table in Oracle 9i:
                ID  E.No.
                =====================
                1   150
                2   150
                3   150
                4   120
                5   120
                6   120
                7   120
                8   110
                9   110
                10 100
                11 100
                12 100
                13 150
                14 150
                15 150
                And i want to create the following result from the above table but
                I am unable to write query to get the same result. However I have
                written the pl/sql program to get the result. Is there anybody
                who can help me to write down qry to get the following result.
                 
                Result:
                 
                From ID      To ID      E.No.    Rec. Count
                ==============================================================
                1                   3         150            3
                4                   7         120            4
                8                   9         110            2
                10               12          150            3
                 
                Thanking you in advance.
                 
                Kamran Khan

                Send instant messages to your online friends http://uk.messenger.yahoo.com

                Offical Website http://www.pkoug.org
                Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                   dba@...
                   ids@...
                   ias@...
                   java@...
                   ocs@...
                   apps@...
                   linux@...

                Pakistan Oracle Users Group




                Celebrate Yahoo!'s 10th Birthday!
                Yahoo! Netrospective: 100 Moments of the Web

                Offical Website http://www.pkoug.org
                Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                   dba@...
                   ids@...
                   ias@...
                   java@...
                   ocs@...
                   apps@...
                   linux@...

                Pakistan Oracle Users Group





                Offical Website http://www.pkoug.org
                Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                   dba@...
                   ids@...
                   ias@...
                   java@...
                   ocs@...
                   apps@...
                   linux@...

                Pakistan Oracle Users Group



              • Ahsan Qaisar
                Try this Query .. select min(id) from_ID, max(id) TO_ID, eno, count(eno) from t1 where ID not in (13,14,15) group by eno order by 1 Regards Ahsan Qaisar _____
                Message 7 of 10 , Mar 3, 2005
                • 0 Attachment

                  Try this Query ….

                   

                  select min(id) from_ID, max(id) TO_ID, eno, count(eno)

                  from t1

                  where ID not in (13,14,15)

                  group by eno

                  order by 1

                   

                  Regards

                  Ahsan Qaisar

                   


                  From: Faisal Ijaz [mailto:faisal_ijaz2000@...]
                  Sent: Thursday, March 03, 2005 3:28 PM
                  To: PkOug@yahoogroups.com
                  Subject: Re: [PkOug] Query Required - Urgent (Your Required Query)

                   

                  This query is very simple but will not serve Mr. Kamrans purpose as it will not show the 150 group twice. Your query will group the 2 "150" Group and treat it as one and from the required output shown by Kamran it repeat twice.

                   

                  Regards

                   

                  Faisal. 

                  Muhammad Kawish Siddiqui <kawish_siddiqui@...> wrote:

                  AOA

                  check following query for this

                   

                  select min(id) from_ID, max(id) TO_ID, eno, count(1) from test group by eno;

                   

                   



                  Best Regards,

                  Muhammad Kawish Siddiqui
                  Sr.Production Support / Software Engineer
                  DWH Division
                  DPS.(www.d-p-s.com)
                  Islamabad .

                  ----- Original Message -----

                  Sent: Wednesday, March 02, 2005 6:16 PM

                  Subject: Re: [PkOug] Query Required - Urgent (Your Required Query)

                   

                  Kamarn,

                   

                  Here is the query that you want:

                   

                  select * from TABLE_NAME where ID in (select min(ID) from table_name group by E_NO);

                   

                  I have verified the results for your query.

                   

                  Best Regards

                   

                  Faisal Ijaz

                   

                   

                   

                   



                  Kamran Khan <kamran_orcl@...> wrote:

                  I have the following table in Oracle 9i:
                  ID  E.No.
                  =====================
                  1   150
                  2   150
                  3   150
                  4   120
                  5   120
                  6   120
                  7   120
                  8   110
                  9   110
                  10 100
                  11 100
                  12 100
                  13 150
                  14 150
                  15 150

                  And i want to create the following result from the above table but
                  I am unable to write query to get the same result. However I have
                  written the pl/sql program to get the result. Is there anybody
                  who can help me to write down qry to get the following result.

                   

                  Result:

                   

                  From ID      To ID      E.No.    Rec. Count
                  ==============================================================
                  1                   3         150            3
                  4                   7         120            4
                  8                   9         110            2
                  10               12          150            3

                   

                  Thanking you in advance.

                   

                  Kamran Khan

                  Send instant messages to your online friends http://uk.messenger.yahoo.com

                  Offical Website http://www.pkoug.org
                  Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                     dba@...
                     ids@...
                     ias@...
                     java@...
                     ocs@...
                     apps@...
                     linux@...

                  Pakistan Oracle Users Group



                  Celebrate Yahoo!'s 10th Birthday!
                  Yahoo! Netrospective: 100 Moments of the Web

                  Offical Website http://www.pkoug.org
                  Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                     dba@...
                     ids@...
                     ias@...
                     java@...
                     ocs@...
                     apps@...
                     linux@...

                  Pakistan Oracle Users Group





                  Offical Website http://www.pkoug.org
                  Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                     dba@...
                     ids@...
                     ias@...
                     java@...
                     ocs@...
                     apps@...
                     linux@...

                  Pakistan Oracle Users Group




                  Yahoo! Groups Sponsor

                  ADVERTISEMENT
                  click here

                  size=1 width="100%" align=center>

                  Celebrate Yahoo!'s 10th Birthday!
                  Yahoo! Netrospective: 100 Moments of the Web

                  Offical Website http://www.pkoug.org
                  Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                     dba@...
                     ids@...
                     ias@...
                     java@...
                     ocs@...
                     apps@...
                     linux@...

                  Pakistan Oracle Users Group




                • Kamran Khan
                  Faisal is right, ENo 150 is repeating twice with ID intervals (1 to 3 and 13 to 15). therefore we can not the group on eno. Please try another logic to resolve
                  Message 8 of 10 , Mar 3, 2005
                  • 0 Attachment
                    Faisal is right, ENo 150 is repeating twice with ID intervals (1 to 3
                    and 13 to 15). therefore we can not the group on eno.
                    Please try another logic to resolve the same.
                    Thanks
                    Kamran

                    --- In PkOug@yahoogroups.com, Faisal Ijaz <faisal_ijaz2000@y...>
                    wrote:
                    > This query is very simple but will not serve Mr. Kamrans purpose as
                    it will not show the 150 group twice. Your query will group the
                    2 "150" Group and treat it as one and from the required output shown
                    by Kamran it repeat twice.
                    >
                    > Regards
                    >
                    > Faisal.
                    >
                    > Muhammad Kawish Siddiqui <kawish_siddiqui@h...> wrote:
                    > AOA
                    > check following query for this
                    >
                    > select min(id) from_ID, max(id) TO_ID, eno, count(1) from test
                    group by eno;
                    >
                    >
                    >
                    >
                    > Best Regards,
                    >
                    > Muhammad Kawish Siddiqui
                    > Sr.Production Support / Software Engineer
                    > DWH Division
                    > DPS.(www.d-p-s.com)
                    > Islamabad.
                    > ----- Original Message -----
                    > From: Faisal Ijaz
                    > To: PkOug@yahoogroups.com
                    > Sent: Wednesday, March 02, 2005 6:16 PM
                    > Subject: Re: [PkOug] Query Required - Urgent (Your Required Query)
                    >
                    >
                    > Kamarn,
                    >
                    > Here is the query that you want:
                    >
                    > select * from TABLE_NAME where ID in (select min(ID) from
                    table_name group by E_NO);
                    >
                    > I have verified the results for your query.
                    >
                    > Best Regards
                    >
                    > Faisal Ijaz
                    >
                    >
                    >
                    >
                    >
                    >
                    > Kamran Khan <kamran_orcl@y...> wrote:
                    > I have the following table in Oracle 9i:
                    > ID E.No.
                    > =====================
                    > 1 150
                    > 2 150
                    > 3 150
                    > 4 120
                    > 5 120
                    > 6 120
                    > 7 120
                    > 8 110
                    > 9 110
                    > 10 100
                    > 11 100
                    > 12 100
                    > 13 150
                    > 14 150
                    > 15 150
                    > And i want to create the following result from the above table but
                    > I am unable to write query to get the same result. However I have
                    > written the pl/sql program to get the result. Is there anybody
                    > who can help me to write down qry to get the following result.
                    >
                    > Result:
                    >
                    > From ID To ID E.No. Rec. Count
                    > ==============================================================
                    > 1 3 150 3
                    > 4 7 120 4
                    > 8 9 110 2
                    > 10 12 150 3
                    >
                    > Thanking you in advance.
                    >
                    > Kamran Khan
                    >
                    > Send instant messages to your online friends
                    http://uk.messenger.yahoo.com
                    >
                    > Offical Website http://www.pkoug.org
                    > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                    > dba@p...
                    > ids@p...
                    > ias@p...
                    > java@p...
                    > ocs@p...
                    > apps@p...
                    > linux@p...
                    >
                    > Pakistan Oracle Users Group
                    >
                    >
                    >
                    >
                    >
                    > ---------------------------------
                    > Celebrate Yahoo!'s 10th Birthday!
                    > Yahoo! Netrospective: 100 Moments of the Web
                    >
                    > Offical Website http://www.pkoug.org
                    > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                    > dba@p...
                    > ids@p...
                    > ias@p...
                    > java@p...
                    > ocs@p...
                    > apps@p...
                    > linux@p...
                    >
                    > Pakistan Oracle Users Group
                    >
                    >
                    >
                    >
                    > Offical Website http://www.pkoug.org
                    > Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                    > dba@p...
                    > ids@p...
                    > ias@p...
                    > java@p...
                    > ocs@p...
                    > apps@p...
                    > linux@p...
                    >
                    > Pakistan Oracle Users Group
                    >
                    >
                    > Yahoo! Groups SponsorADVERTISEMENT
                    >
                    >
                    > ---------------------------------
                    > Yahoo! Groups Links
                    >
                    > To visit your group on the web, go to:
                    > http://groups.yahoo.com/group/PkOug/
                    >
                    > To unsubscribe from this group, send an email to:
                    > PkOug-unsubscribe@yahoogroups.com
                    >
                    > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
                    Service.
                    >
                    >
                    >
                    > ---------------------------------
                    > Celebrate Yahoo!'s 10th Birthday!
                    > Yahoo! Netrospective: 100 Moments of the Web
                  • Sikandar Hayat
                    Hi, Here is a test resulting same as you mentioned, SCOTT drop table t; Table dropped. SCOTT create table t(id number(2), value number(3)); Table created.
                    Message 9 of 10 , Mar 3, 2005
                    • 0 Attachment
                      Hi,
                      Here is a test resulting same as you mentioned,

                      SCOTT > drop table t;

                      Table dropped.

                      SCOTT > create table t(id number(2), value number(3));

                      Table created.

                      insert into t values( 1,150);
                      insert into t values( 2,150);
                      insert into t values( 3,150);
                      insert into t values( 4,120);
                      insert into t values( 5,120);
                      insert into t values( 6,120);
                      insert into t values( 7,120);
                      insert into t values( 8,110);
                      insert into t values( 9,110);
                      insert into t values( 10,100);
                      insert into t values( 11,100);
                      insert into t values( 12,10);
                      insert into t values( 13,10);
                      insert into t values( 14,100);
                      insert into t values( 15,100);

                      SCOTT > select * from t order by 1;

                      ID VALUE
                      ---------- ----------
                      1 150
                      2 150
                      3 150
                      4 120
                      5 120
                      6 120
                      7 120
                      8 110
                      9 110
                      10 100
                      10 100
                      11 100
                      12 10
                      13 10
                      14 100
                      15 100

                      16 rows selected.

                      SCOTT > select min(id), max(id), value, count(*) cnt
                      from t group by value;

                      MIN(ID) MAX(ID) VALUE CNT
                      ---------- ---------- ---------- ----------
                      12 13 10 2
                      10 15 100 5
                      8 9 110 2
                      4 7 120 4
                      1 3 150 3

                      SCOTT >

                      Please let us know if still any issue. I think earlier
                      all gave the same solution and seems to be close to
                      your requirement.

                      Regards,
                      Sikandar Hayat

                      --- Ahsan Qaisar <ahsan.ahmed@...> wrote:
                      > Try this Query ..
                      >
                      >
                      >
                      > select min(id) from_ID, max(id) TO_ID, eno,
                      > count(eno)
                      >
                      > from t1
                      >
                      > where ID not in (13,14,15)
                      >
                      > group by eno
                      >
                      > order by 1
                      >
                      >
                      >
                      > Regards
                      >
                      > Ahsan Qaisar
                      >
                      >
                      >
                      > _____
                      >
                      > From: Faisal Ijaz [mailto:faisal_ijaz2000@...]
                      >
                      > Sent: Thursday, March 03, 2005 3:28 PM
                      > To: PkOug@yahoogroups.com
                      > Subject: Re: [PkOug] Query Required - Urgent (Your
                      > Required Query)
                      >
                      >
                      >
                      > This query is very simple but will not serve Mr.
                      > Kamrans purpose as it will
                      > not show the 150 group twice. Your query will group
                      > the 2 "150" Group and
                      > treat it as one and from the required output shown
                      > by Kamran it repeat
                      > twice.
                      >
                      >
                      >
                      > Regards
                      >
                      >
                      >
                      > Faisal.
                      >
                      > Muhammad Kawish Siddiqui
                      > <kawish_siddiqui@...> wrote:
                      >
                      > AOA
                      >
                      > check following query for this
                      >
                      >
                      >
                      > select min(id) from_ID, max(id) TO_ID, eno, count(1)
                      > from test group by eno;
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      > Best Regards,
                      >
                      > Muhammad Kawish Siddiqui
                      > Sr.Production Support / Software Engineer
                      > DWH Division
                      > DPS.(www.d-p-s.com)
                      > Islamabad.
                      >
                      > ----- Original Message -----
                      >
                      > From: Faisal <mailto:faisal_ijaz2000@...>
                      > Ijaz
                      >
                      > To: PkOug@yahoogroups.com
                      >
                      > Sent: Wednesday, March 02, 2005 6:16 PM
                      >
                      > Subject: Re: [PkOug] Query Required - Urgent (Your
                      > Required Query)
                      >
                      >
                      >
                      > Kamarn,
                      >
                      >
                      >
                      > Here is the query that you want:
                      >
                      >
                      >
                      > select * from TABLE_NAME where ID in (select min(ID)
                      > from table_name group
                      > by E_NO);
                      >
                      >
                      >
                      > I have verified the results for your query.
                      >
                      >
                      >
                      > Best Regards
                      >
                      >
                      >
                      > Faisal Ijaz
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      > Kamran Khan <kamran_orcl@...> wrote:
                      >
                      > I have the following table in Oracle 9i:
                      > ID E.No.
                      > =====================
                      > 1 150
                      > 2 150
                      > 3 150
                      > 4 120
                      > 5 120
                      > 6 120
                      > 7 120
                      > 8 110
                      > 9 110
                      > 10 100
                      > 11 100
                      > 12 100
                      > 13 150
                      > 14 150
                      > 15 150
                      >
                      > And i want to create the following result from the
                      > above table but
                      > I am unable to write query to get the same result.
                      > However I have
                      > written the pl/sql program to get the result. Is
                      > there anybody
                      > who can help me to write down qry to get the
                      > following result.
                      >
                      >
                      >
                      > Result:
                      >
                      >
                      >
                      > From ID To ID E.No. Rec. Count
                      >
                      ==============================================================
                      > 1 3 150 3
                      > 4 7 120 4
                      > 8 9 110 2
                      > 10 12 150 3
                      >
                      >
                      >
                      > Thanking you in advance.
                      >
                      >
                      >
                      > Kamran Khan
                      >
                      > Send instant messages to your online friends
                      > http://uk.messenger.yahoo.com
                      >
                      > Offical Website http://www.pkoug.org
                      > <http://www.pkoug.org/>
                      > Special Interest Groups (DBA, IDS, IAS, Java, OCS,
                      > Apps, Linux)
                      > dba@...
                      > ids@...
                      > ias@...
                      > java@...
                      > ocs@...
                      > apps@...
                      > linux@...
                      >
                      > Pakistan Oracle Users Group
                      >
                      >
                      >
                      >
                      > _____
                      >
                      >
                      > Celebrate Yahoo!'s 10th Birthday!
                      > Yahoo! Netrospective: 100
                      > <http://birthday.yahoo.com/netrospective/>
                      >
                      === message truncated ===

                      =====
                      Assalamu Alaikum!



                      Thanks.

                      Regards,
                      Sikandar Hayat

                      Send instant messages to your online friends http://uk.messenger.yahoo.com
                    • Faisal Ijaz
                      Yes there are issues...Again if u see Kamran s initial email u will see that 150 appear in 2 seperate blocks...But in ur Query it will be grouped as 1 block
                      Message 10 of 10 , Mar 3, 2005
                      • 0 Attachment
                        Yes there are issues...Again if u see Kamran's initial email u will see that 150 appear in 2 seperate blocks...But in ur Query it will be grouped as 1 block e.g. Take example of 100 in ur test. It appears twice and so it shoud appear twice in ur resultant query. I hope u get what i am saying....
                         


                        Sikandar Hayat <sikandarocp@...> wrote:

                        Hi,
                        Here is a test resulting same as you mentioned,

                        SCOTT > drop table t;

                        Table dropped.

                        SCOTT > create table t(id number(2), value number(3));

                        Table created.

                        insert into t values( 1,150);
                        insert into t values( 2,150);
                        insert into t values( 3,150);
                        insert into t values( 4,120);
                        insert into t values( 5,120);
                        insert into t values( 6,120);
                        insert into t values( 7,120);
                        insert into t values( 8,110);
                        insert into t values( 9,110);
                        insert into t values( 10,100);
                        insert into t values( 11,100);
                        insert into t values( 12,10);
                        insert into t values( 13,10);
                        insert into t values( 14,100);
                        insert into t values( 15,100);

                        SCOTT > select * from t order by 1;

                                ID      VALUE
                        ---------- ----------
                                 1        150
                                 2        150
                                 3        150
                                 4        120
                                 5        120
                                 6        120
                                 7        120
                                 8        110
                                 9        110
                                10        100
                                10        100
                                11        100
                                12         10
                                13         10
                                14        100
                                15        100

                        16 rows selected.

                        SCOTT > select min(id), max(id), value, count(*) cnt
                        from t group by value;

                           MIN(ID)    MAX(ID)      VALUE        CNT
                        ---------- ---------- ---------- ----------
                                12         13         10          2
                                10         15        100          5
                                 8          9        110          2
                                 4          7        120          4
                                 1          3        150          3

                        SCOTT >

                        Please let us know if still any issue. I think earlier
                        all gave the same solution and seems to be close to
                        your requirement.

                        Regards,
                        Sikandar Hayat

                        --- Ahsan Qaisar <ahsan.ahmed@...> wrote:
                        > Try this Query ..
                        >

                        >
                        > select min(id) from_ID, max(id) TO_ID, eno,
                        > count(eno)
                        >
                        > from t1
                        >
                        > where ID not in (13,14,15)
                        >
                        > group by eno
                        >
                        > order by 1
                        >

                        >
                        > Regards
                        >
                        > Ahsan Qaisar
                        >

                        >
                        >   _____ 
                        >
                        > From: Faisal Ijaz [mailto:faisal_ijaz2000@...]
                        >
                        > Sent: Thursday, March 03, 2005 3:28 PM
                        > To: PkOug@yahoogroups.com
                        > Subject: Re: [PkOug] Query Required - Urgent (Your
                        > Required Query)
                        >

                        >
                        > This query is very simple but will not serve Mr.
                        > Kamrans purpose as it will
                        > not show the 150 group twice. Your query will group
                        > the 2 "150" Group and
                        > treat it as one and from the required output shown
                        > by Kamran it repeat
                        > twice.
                        >

                        >
                        > Regards
                        >

                        >
                        > Faisal.
                        >
                        > Muhammad Kawish Siddiqui
                        > <kawish_siddiqui@...> wrote:
                        >
                        > AOA
                        >
                        > check following query for this
                        >

                        >
                        > select min(id) from_ID, max(id) TO_ID, eno, count(1)
                        > from test group by eno;
                        >

                        >

                        >
                        >
                        >
                        > Best Regards,
                        >
                        > Muhammad Kawish Siddiqui
                        > Sr.Production Support / Software Engineer
                        > DWH Division
                        > DPS.(www.d-p-s.com)
                        > Islamabad.
                        >
                        > ----- Original Message -----
                        >
                        > From: Faisal <mailto:faisal_ijaz2000@...>
                        > Ijaz
                        >
                        > To: PkOug@yahoogroups.com
                        >
                        > Sent: Wednesday, March 02, 2005 6:16 PM
                        >
                        > Subject: Re: [PkOug] Query Required - Urgent (Your
                        > Required Query)
                        >

                        >
                        > Kamarn,
                        >

                        >
                        > Here is the query that you want:
                        >

                        >
                        > select * from TABLE_NAME where ID in (select min(ID)
                        > from table_name group
                        > by E_NO);
                        >

                        >
                        > I have verified the results for your query.
                        >

                        >
                        > Best Regards
                        >

                        >
                        > Faisal Ijaz
                        >

                        >

                        >

                        >

                        >
                        >
                        >
                        > Kamran Khan <kamran_orcl@...> wrote:
                        >
                        > I have the following table in Oracle 9i:
                        > ID  E.No.
                        > =====================
                        > 1   150
                        > 2   150
                        > 3   150
                        > 4   120
                        > 5   120
                        > 6   120
                        > 7   120
                        > 8   110
                        > 9   110
                        > 10 100
                        > 11 100
                        > 12 100
                        > 13 150
                        > 14 150
                        > 15 150
                        >
                        > And i want to create the following result from the
                        > above table but
                        > I am unable to write query to get the same result.
                        > However I have
                        > written the pl/sql program to get the result. Is
                        > there anybody
                        > who can help me to write down qry to get the
                        > following result.
                        >

                        >
                        > Result:
                        >

                        >
                        > From ID      To ID      E.No.    Rec. Count
                        >
                        ==============================================================
                        > 1                   3         150            3
                        > 4                   7         120            4
                        > 8                   9         110            2
                        > 10               12          150            3
                        >

                        >
                        > Thanking you in advance.
                        >

                        >
                        > Kamran Khan
                        >
                        > Send instant messages to your online friends
                        > http://uk.messenger.yahoo.com
                        >
                        > Offical Website http://www.pkoug.org
                        > <http://www.pkoug.org/>
                        > Special Interest Groups (DBA, IDS, IAS, Java, OCS,
                        > Apps, Linux)
                        >    dba@...
                        >    ids@...
                        >    ias@...
                        >    java@...
                        >    ocs@...
                        >    apps@...
                        >    linux@...
                        >
                        > Pakistan Oracle Users Group
                        >
                        >
                        >
                        >
                        >   _____ 
                        >
                        >
                        > Celebrate Yahoo!'s 10th Birthday!
                        > Yahoo! Netrospective: 100
                        > <http://birthday.yahoo.com/netrospective/>
                        >
                        === message truncated ===

                        =====
                        Assalamu Alaikum!



                        Thanks.

                        Regards,
                        Sikandar Hayat

                        Send instant messages to your online friends http://uk.messenger.yahoo.com


                        Offical Website http://www.pkoug.org
                        Special Interest Groups (DBA, IDS, IAS, Java, OCS, Apps, Linux)
                           dba@...
                           ids@...
                           ias@...
                           java@...
                           ocs@...
                           apps@...
                           linux@...

                        Pakistan Oracle Users Group




                        Celebrate Yahoo!'s 10th Birthday!
                        Yahoo! Netrospective: 100 Moments of the Web

                      Your message has been successfully submitted and would be delivered to recipients shortly.