13 Replies Latest reply: Oct 20, 2013 10:59 PM by 875438 RSS

    Query regarding sorting

    875438

      Hi everyone,

       

      in the below table:

       

      no   id        desc    modified_by

      1      123      ffff       6666

      2      345     jjjjjjj       5678

      3       56       tyi        hyi

      4       4567   jklop    890

      5       5678   uiop     jopu

      ....

      I want to display all the records that have been modified by 5678  till the last record of the table

      so the output should be:

       

      no   id        desc    modified_by

      2      345     jjjjjjj       5678

      3       56       tyi        hyi

      4       4567   jklop    890

      5       5678   uiop     jopu

      .....

      Kindly help

        • 1. Re: Query regarding sorting
          Ramin Hashimzadeh

          easy way (if you need ordered by no field)

          select * from t

          where t.no >= (select no from t where modified_by = '5678')

           

          ----

          Ramin Hashimzade

          • 2. Re: Query regarding sorting
            Ramin Hashimzadeh

            second way, with one select :

             

            select *

              from (select q.*, lag(m ignore nulls, 1, m) over(order by q.no) rn

                      from

                      (select t.*, decode(modified_by, '5678', 1) m from <mytable> t)

                   q)

            where rn = 1

             

            ----

            Ramin Hashimzade

            • 3. Re: Query regarding sorting
              875438

              Hi Ramin,

               

              Thanks for the input. But I require all the data modified by '5678' onwards i.e even data of hyi, 890 and jopu.

              • 4. Re: Query regarding sorting
                Mukesh75

                Hi,

                try this code if no is sequentially increasing with the data.

                 

                with t as (

                select 1 no, 123 id, 'ffff' desc1,'6666' modified_by from dual

                union all

                select 2 , 345 , 'jjjjjjj' ,'5678'  from dual

                union all

                select 3 , 56 , 'tyi' ,'hyi'  from dual

                union all

                select 4 , 4567 , 'jklop' ,'890' from dual

                union all

                select 5 , 5678 , 'uiop' ,'jopu'  from dual)

                select * from t where no >= (select no from t where modified_by='5678')

                • 5. Re: Query regarding sorting
                  Ramin Hashimzadeh

                  did you check my queries??? I didn't understand, what is wrong???

                   

                  ----

                  Ramin Hashimzade

                  • 6. Re: Query regarding sorting
                    875438

                    If I need to display all the data, starting from 5.... can i use 'Like' in the below query.

                     

                    select *

                      from (select q.*, lag(m ignore nulls, 1, m) over(order by q.no) rn

                              from

                              (select t.*, decode(modified_by, '5678', 1) m from <mytable> t)

                           q)

                    where rn = 1

                    • 7. Re: Query regarding sorting
                      DrabJay

                      select no, id, desc, modified_by

                      from (select no, id, desc, modified_by, min(case when modified_by = '5678' then no else null end) over () min_no

                            from t)

                      where no >= min_no

                      • 8. Re: Query regarding sorting
                        Etbin

                        with

                        data_table as

                        (select 1 no,'123' id,'ffff' descr,'6666' modified_by from dual union all

                        select 2,'345','jjjjjjj','5678' from dual union all

                        select 3,'56','tyi','hyi' from dual union all

                        select 4,'4567','jklop','890' from dual union all

                        select 5,'5678','uiop','jopu' from dual union all

                        select 6,'38','gf','5678' from dual union all

                        select 7,'458','k','5678' from dual union all

                        select 8,'1138','tz','jopu' from dual union all

                        select 9,'228','nzg','jopu' from dual

                        )

                        select *

                          from data_table

                        where no >= (select min(no)

                                        from data_table

                                       where modified_by = :modifier

                                     )

                         

                        NOIDDESCRMODIFIED_BY
                        2345jjjjjjj5678
                        356tyihyi
                        44567jklop890
                        55678uiopjopu
                        638gf5678
                        7458k5678
                        81138tzjopu
                        9228nzgjopu

                         

                        Regards

                         

                        Etbin

                        • 9. Re: Query regarding sorting
                          chris227

                          Date structure is not good and should be normalized.

                          However

                           

                          with t as (

                           

                          select 1 no, 123 id, 'ffff' desc1,'6666' modified_by from dual

                           

                          union all

                           

                          select 2 , 345 , 'jjjjjjj' ,'5678'  from dual

                           

                          union all

                           

                          select 3 , 56 , 'tyi' ,'hyi'  from dual

                           

                          union all

                           

                          select 4 , 4567 , 'jklop' ,'890' from dual

                           

                          union all

                           

                          select 5 , 5678 , 'uiop' ,'jopu'  from dual)

                           

                          select * from t

                          where

                          nvl(

                            length(

                              translate (id

                                      , chr(0)||'5678'

                                      , chr(0))

                              )

                          , 0)

                          < length(id)

                           

                          Hm, think it's a misread from my side ...

                          • 10. Re: Query regarding sorting
                            875438

                            Hi,

                             

                            Please see the below table

                             

                            I require all the data that has been modified by any user starting by the number 5......till all the records in the end i.e starting from 574503, 574508, ipru21945 ......... and so on till the last record in the table.

                             

                            noidshort_desccodedescmodified_by
                            4455669STDJ2Jet Std3457906
                            4465714STDJ2Jet Std574503
                            4475841IRQIRUWrong App574508
                            1655614815Non MedicalIDMIdentity Proofipru21945
                            1655713992STDSTSTPipru21489
                            3956722900Non MedicaIDMIdentity Proofipru19782
                            3956822900Non MedicaADPAddress Proofipru19782
                            3956922900Non MedicalKYPKYC of Payoripru19782
                            4154924076STDSTSTPipru20575
                            4155424063Non MedicalADPAddress Proofipru20395
                            4805744STDSTSTP534826
                            4815744STDJ2Jet Std

                            534826

                            • 11. Re: Query regarding sorting
                              JonWat

                              You already have several answers that do what you asked -- Etbin's probably clearest -- so I'm not sure why you are asking the same question again with different "data". But you also need to know that unless you use ORDER BY the rows in the table can be returned to you in any order. Without many changes to the table things will normally come in the same order, but at a certain point you are very likely to find that a block of rows that have been recently entered, that you might expect to be at the "end of the table" are actually the first rows that are returned. That will probably cause you trouble. Since the "data" you have given is obviously not sorted by any of the fields you have shown, you may have a problem.

                               

                              Jon

                              • 12. Re: Query regarding sorting
                                Stew Ashton

                                If you have version 12c, it's very easy:

                                 

                                WITH data_table AS (
                                  select 1 no,'123' id,'ffff' descr,'6666' modified_by from dual union all
                                  select 2,'345','jjjjjjj','5678' from dual union all
                                  select 3,'56','tyi','hyi' from dual union all
                                  select 4,'4567','jklop','890' from dual union all
                                  select 5,'5678','uiop','jopu' from dual union all
                                  select 6,'38','gf','5678' from dual union all
                                  select 7,'458','k','5678' from dual union all
                                  select 8,'1138','tz','jopu' from dual union all
                                  SELECT 9,'228','nzg','jopu' FROM dual
                                )
                                SELECT * FROM data_table
                                match_recognize(
                                  ORDER BY NO
                                  ALL ROWS per match
                                  pattern (A b*)
                                  define a as modified_by = '5678'
                                );
                                

                                 

                                NOIDDESCRMODIFIED_BY
                                2345jjjjjjj5678
                                356tyihyi
                                44567jklop890
                                55678uiopjopu
                                638gf5678
                                7458k5678
                                81138tzjopu
                                9228nzgjopu
                                • 13. Re: Query regarding sorting
                                  875438

                                  Hi,

                                   

                                  Thanks for all your replies, but as you can see from my previous post, In the modified_by column, it is not necessary that only the ID 5678 will modify it. it can be modified by also 574503, 574508 or ipru21945. Can I use "LIKE" keyword in anyway here ?