2 Replies Latest reply on Jan 13, 2017 4:25 PM by rp0428

    I want to create line numbers in a sql query from inside developer.

    876864

      Is there a quick an easy way to create line numbers on the fly (without Creating a Sequence or using PL*SQL) for a SQL query executed in SQL Developer worksheet.

      Example:

       

      SELECT rownum,substr(C_MANUFACTURE_NAME,1,21) manu,C_COMMON_NAME,rownum,C_COUNTRY_MANUFACTURE

      from cologne_master

      order by C_COMMON_NAME

       

      Yields This

       

              16 Thierry Mugler Parfum Angel for Men                16 FRANCE        

               9 Francis Kurkdjian     Aqua Vitae                    9 FRANCE        

              34 Aramis                Aramis for Men               34 SWITZERLAND   

              19 BVLGari               BVLGARI for men              19 SWITZERLAND   

              23 Hermis                BelAmi                       23 FRANCE        

              32 Bijan                 Bijan for Men                32 USA           

              15 Charolina Herrera     CH for Men                   15 SPAIN         

               2 Frederic Malle        Carnal Flower                 2 FRANCE        

              35 Chaleur               Chaleur D Animale            35 USA           

              40 Olfactive Studio      Chambre Noire                40 FRANCE        

              33 Ralph Lauren          Chaps                        33 USA           

              25 Duc De Vervins        DV                           25 FRANCE        

              27 Dolce Gabana          Dolce Gabana Men             27 FRANCE        

              20 Pasha de Cartier      Fraicheur Menthe             20 FRANCE        

              30 Jean Paul Gautier     Gautier 2                    30 FRANCE        

              36 Givenchy              Givenchy Gentleman           36 FRANCE       

       

      as you can see there are line number but there in a crazy sequence. I want to sort by a real column name and have the rownum pseudo numbers be sorted as well. So the output would looks like this with line numbers 1-16 in sequential order

       

      1 Thierry Mugler Parfum Angel for Men                16 FRANCE        

      2 Francis Kurkdjian     Aqua Vitae                    9 FRANCE        

      3 Aramis                Aramis for Men               34 SWITZERLAND   

      4 BVLGari               BVLGARI for men              19 SWITZERLAND   

      5 Hermis                BelAmi                       23 FRANCE        

      6 Bijan                 Bijan for Men                32 USA           

      7 Charolina Herrera     CH for Men                   15 SPAIN         

      8 Frederic Malle        Carnal Flower                 2 FRANCE        

      9 Chaleur               Chaleur D Animale            35 USA           

      10 Olfactive Studio      Chambre Noire                40 FRANCE        

      11 Ralph Lauren          Chaps                        33 USA           

      12 Duc De Vervins        DV                           25 FRANCE        

      13 Dolce Gabana          Dolce Gabana Men             27 FRANCE        

      14 Pasha de Cartier      Fraicheur Menthe             20 FRANCE        

      15 Jean Paul Gautier     Gautier 2                    30 FRANCE        

      16 Givenchy              Givenchy Gentleman           36 FRANCE