1 2 Previous Next 20 Replies Latest reply on Oct 11, 2012 2:18 PM by AlbertoFaenza

    Reg: small query

    948062
      Hi,

      i have a table with records.
                           (Min)        (Min)         (Max)              (Max)
      securityIntid     EffDt             EffTm         ExpDt               ExpTm
      5136              20120108         4:47           20121009           4:45
      5136              20120108         4:47           20121009           4:48
      i have to get a query like for a particular Securiity id, Min(EFFDt), Min(efftm), max(expdt), max(exptm).

      here in above ex:
      for a particular Securityid(5136) two records are there but i want second record as 5136 second one in last filter column(ExpTm) time is 4:48 which is max then 4: 45

      so output i need like:
      securityIntid     EffDt             EffTm         ExpDt               ExpTm
      5136              20120108         4:47           20121009           4:48
        • 1. Re: Reg: small query
          Nimish Garg
          select
               securityIntid,
               min(EffDt),
               min(EffTm),
               max(ExpDt),
               max(ExpTm)
          from
               your_table
          where
               securityIntid=5136
          group by
               securityIntid
          • 2. Re: Reg: small query
            Frank Kulash
            Hi,
            945059 wrote:
            Hi,

            i have a table with records.
            (Min)        (Min)         (Max)              (Max)
            securityIntid     EffDt             EffTm         ExpDt               ExpTm
            5136              20120108         4:47           20121009           4:45
            5136              20120108         4:47           20121009           4:48
            i have to get a query like for a particular Securiity id, Min(EFFDt), Min(efftm), max(expdt), max(exptm).

            here in above ex:
            for a particular Securityid(5136) two records are there but i want second record as 5136 second one in last filter column(ExpTm) time is 4:48 which is max then 4: 45

            so output i need like:
            securityIntid     EffDt             EffTm         ExpDt               ExpTm
            5136              20120108         4:47           20121009           4:48
            You already posted the SELECT clause:
            ... Securiity id, Min(EFFDt), Min(efftm), max(expdt), max(exptm).
            Here's the full query:
            SELECT       securityintid
            ,       MIN (effdt)     AS min_effdt
            ,       MIN (efftm)     AS min_efftm
            ,       MAX (expdt)     AS max_exptm
            ,       MAX (exptm)     AS max_exptm
            FROM       table_x
            WHERE       securityinit     IN (5136)
            GROUP BY  securityintid
            ;
            Why do you have separate columns for effdt and efftm? Why not have a single DATE column? The same goes for expdt and exptm.
            Among other advantages, that would make it easier to find, for exampe, the MIN (effdt) and the efftm associated with it. With separate columns, you would have to do something like:
            SELECT       securityintid
            ,       MIN (effdt)     AS min_effdt
            ,       MIN (efftm) KEEP (DENSE_RANK FIRST ORDER BY effdt)
                           AS min_efftm
            ,       MAX (expdt)     AS max_exptm
            ,       MAX (exptm) KEEP (DENSE_RANK LAST  ORDER BY expdt)
                           AS max_exptm
            FROM       table_x
            WHERE       securityinit     IN (5136)
            GROUP BY  securityintid
            ;
            Edited by: Frank Kulash on Oct 11, 2012 5:39 AM
            Added query with FIRST and LAST.
            • 3. Re: Reg: small query
              AlbertoFaenza
              Hi,

              Post table structure and sample data and reformulate your question.
              It's not clear what you get and what you expect to get.

              Regards.
              Al
              • 4. Re: Reg: small query
                jeneesh
                Nimish Garg wrote:
                select
                     securityIntid,
                     min(EffDt),
                     min(EffTm),
                     max(ExpDt),
                     max(ExpTm)
                from
                     your_table
                where
                     securityIntid=5136
                group by
                     securityIntid
                I dont think this will work for time...

                12:50 will be considered as less than 4:50 when it is a varchar2..
                • 5. Re: Reg: small query
                  Nag Aswadhati
                  <CODE>

                  SELECT securityIntid,MIN(EffDt),MIN(EffTm),MAX( ExpDt),MAX( ExpTm)
                  FROM TABLE
                  GROUP BY securityIntid;

                  </CODE>

                  For more information give me table name sample insert statements and data.
                  • 6. Re: Reg: small query
                    948062
                    HI,

                    i need for a particular secrityid, Min(effdt),min(effftm), max(expdt), max(exptm).



                    Thanks
                    • 7. Re: Reg: small query
                      Nag Aswadhati
                      Please place where clause in my previous query


                      WHERE secrityid = 5136
                      • 8. Re: Reg: small query
                        948062
                        i have given 5136 as a example there can be 5134,5139 as millions of records are there.
                        • 9. Re: Reg: small query
                          AlbertoFaenza
                          945059 wrote:
                          i have given 5136 as a example there can be 5134,5139 as millions of records are there.
                          For such a simple problem you are not explaining clearly what you need. Why don't you spend more time typing and explaining the case.

                          I still don't understand if you want one record having MIN(EffDt),MIN(EffTm),MAX( ExpDt),MAX( ExpTm) for

                          a) a certain securityIntid
                          b) all securityIntid values.

                          Could you try to explain your requirements better?

                          Please provide sample data and expected output.

                          Regards.
                          Al
                          • 10. Re: Reg: small query
                            948062
                            suppose my table name is x

                            contanis record like:
                            securityid      EffDt          EffTm      ExpDT        ExpTM
                            
                            5136            20120108       9:47        20121009    9:45
                            
                            5136            20120108       9:47        20121009    9:48
                            
                            5135            20120108       9:47        20121009    9:46
                            
                            5135            20120109       9:48        20121008    9:45
                            my output should come like:
                            securityid      EffDt          EffTm      ExpDT        ExpTM
                            5136            20120108       9:47        20121009    9:48
                            5135            20120108       9:47        20121009    9:46
                            My requirement is i have given above sample Data, in that for a particular SecurityIntid i need Min(EffDt), Min(EffTm), Max(ExpDt), Max(ExpTm).
                            • 11. Re: Reg: small query
                              948062
                              Hi,

                              I have a table like
                              securityid      EffDt          EffTm      ExpDT        ExpTM        syscode    name
                               
                              5136            20120108       9:47        20121009    9:45         0           hari
                               
                              5136            20120108       9:47        20121009    9:48         1          ravi
                               
                              5135            20120108       9:47        20121009    9:46         2          suresh
                               
                              5135            20120109       9:48        20121008    9:45         3           ajay
                              i have to select for a particular security id min(effDt), min(EffTm), Max(ExpDT), MAx(ExpTM) and also i have to select syscode and name column without any condition.


                              pls suggest.
                              • 12. Re: Reg: small query
                                jeneesh
                                SQL> select * from x;
                                
                                SECURITYID EFFDT    EFFTM    EXPDT    EXPTM
                                ---------- -------- -------- -------- --------
                                      5136 20120108 9:47     20121009 9:45
                                      5136 20120108 9:47     20121009 9:48
                                      5135 20120108 9:47     20121009 9:46
                                      5135 20120109 9:48     20121008 9:45
                                      5135 20120109 10:48    20121008 10:45
                                
                                SQL> select securityid,ltrim(min(lpad(efftm,5,'0')),'0') efftm,
                                  2         max(expdt) expdt,ltrim(max(lpad(exptm,5,'0')),'0') exptm
                                  3  from x
                                  4  group by securityid
                                  5  order by 1;
                                
                                SECURITYID EFFTM EXPDT    EXPTM
                                ---------- ----- -------- -----
                                      5135 9:47  20121009 10:45
                                      5136 9:47  20121009 9:48
                                Edited by: jeneesh on Oct 11, 2012 5:31 PM
                                Between, why are you storing date and time in seperate columns?
                                Why are you storing date and time as strimg?
                                • 13. Re: Reg: small query
                                  948062
                                  Hi Jeenesh,


                                  iHi,

                                  I have a table like
                                  securityid      EffDt          EffTm      ExpDT        ExpTM        syscode    name
                                   
                                  5136            20120108       9:47        20121009    9:45         0           hari
                                   
                                  5136            20120108       9:47        20121009    9:48         1          ravi
                                   
                                  5135            20120108       9:47        20121009    9:46         2          suresh
                                   
                                  5135            20120109       9:48        20121008    9:45         3           ajay
                                   
                                  i have to select for a particular security id min(effDt), min(EffTm), Max(ExpDT), MAx(ExpTM) and also i have to select syscode and name column without any condition.


                                  pls suggest.
                                  • 14. Re: Reg: small query
                                    AlbertoFaenza
                                    945059 wrote:
                                    my output should come like:
                                    securityid      EffDt          EffTm      ExpDT        ExpTM
                                    5136            20120108       9:47        20121009    9:48
                                    5135            20120108       9:47        20121009    9:46
                                    My requirement is i have given above sample Data, in that for a particular SecurityIntid i need Min(EffDt), Min(EffTm), Max(ExpDt), Max(ExpTm).
                                    Then the answer from Nag Aswadhati:
                                    SELECT securityIntid,MIN(EffDt),MIN(EffTm),MAX( ExpDt),MAX( ExpTm)
                                    FROM TABLE
                                    GROUP BY securityIntid;
                                    {code}
                                    
                                    is doing exactly this. What's the problem with that?
                                    
                                    By the way, if EffDt and EffTm is referred to a specific date and time, they should be in one column only having date format.
                                    
                                    What do you expect to have back if I change EffTm to 9:55 on first 5135 records like this:
                                    
                                    
                                    {code}
                                    securityid      EffDt          EffTm      ExpDT        ExpTM
                                     
                                    5136            20120108       9:47        20121009    9:45
                                     
                                    5136            20120108       9:47        20121009    9:48
                                     
                                    5135            20120108       9:55        20121009    9:46
                                     
                                    5135            20120109       9:48        20121008    9:45
                                    {code}
                                    
                                    Using the query above you will get the MIN value of EffTm for each securityid so your output will be:
                                    
                                    {code}
                                    securityid      EffDt          EffTm      ExpDT        ExpTM
                                    5136            20120108       9:47        20121009    9:48
                                    5135            20120108       9:48        20121009    9:46
                                    {code}
                                    
                                    Is this what you want?
                                    
                                    Regards.
                                    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    1 2 Previous Next