1 2 Previous Next 20 Replies Latest reply: Oct 11, 2012 9:18 AM by AlbertoFaenza Go to original post RSS
      • 15. Re: Reg: small query
        AlbertoFaenza
        945059 wrote:
        Hi Jeenesh,


        iHi,

        I have a table like
        Please post the table structure.
        >
        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.
        Which syscode to you want to print for 5136 and 5135? 0 or 1 for 5136? 2 or 3 for 5135? What is the logic to choose it?
        Same for name, you have 2 different names for each securityid. Which one do you want to print and which logic to choose it?

        You should know which are the requirements before writing the query.

        Regards.
        Al
        • 16. Re: Reg: small query
          948062
          Hi,
          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.

          my output should be like this:
          securityid      EffDt          EffTm      ExpDT        ExpTM        syscode    name
          5136            20120108       9:47        20121009    9:48         1          ravi
          5135            20120108        9:47       20121009    9:46         2          suresh
          i mean if i'am using ur query:
          i have to use
          select securityIntid, min(Effdt),min(Efftm),max(expDt),max(expTm) from table_x
          group by securityIntid.
          but i need to select syscode and name column also in my select statement:

          select securityIntid,syscode,name,min(Effdt),min(Efftm),max(expDt),max(expTm) from table_x
          group by securityIntid.


          it will not work because whtvr i used in select statement with aggregate function i have to use in group by clause.

          so suggest me how to do



          Thanks
          • 17. Re: Reg: small query
            948062
            one more think for syscode and name column there is no condition.
            • 18. Re: Reg: small query
              948062
              whatever coming for particular securityid, with min(EffDt), Min(EffTm), max(ExpDT), max(ExpTM), that row data will be selected for syscode and name also.
              • 19. Re: Reg: small query
                Paul  Horth
                Please post a create table statement and inserts for your sample data so we can help.

                BTW do not have separate columns for date and time and, if as i suspect, they are varchar2 columns, then
                this will be a nightmare to make work...

                time 4:51
                time 17:21

                which is the earlier time if doing a string comparison?
                • 20. Re: Reg: small query
                  AlbertoFaenza
                  945059 wrote:
                  whatever coming for particular securityid, with min(EffDt), Min(EffTm), max(ExpDT), max(ExpTM), that row data will be selected for syscode and name also.
                  I guess you did not understand exactly how the aggregate functions work.

                  if you use min(EffDt), Min(EffTm), max(ExpDT), max(ExpTM) and group by securityid the columns might not come all from the same records.

                  This is what I was trying to explain you.

                  Let me show you an example:

                  Suppose you have 4 records from the same securityid:
                  securityid      EffDt          EffTm      ExpDT        ExpTM       syscode    name  
                                                                                                      
                  5136            20120108       9:47        20121009    9:45        0          hari 
                                                                                                      
                  5136            20120109       9:45        20121009    9:48        1          ravi  
                                                                                                      
                  5136            20120110       9:47        20121011    9:46        2          suresh
                                                                                                      
                  5136            20120111       9:48        20121008    9:50        3          ajay 
                  if you write a query like:
                  select
                       securityIntid,
                       min(EffDt),
                       min(EffTm),
                       max(ExpDt),
                       max(ExpTm)
                  from
                       your_table
                  group by
                       securityIntid
                  
                  the output will be:
                  securityIntid     EffDt             EffTm         ExpDt               ExpTm
                  5136              20120108          9:45          20121011            9:50
                  The 4 columns are coming from 4 different rows. You are not selecting only one row.
                  EffDt is coming from row #1, EffTm is coming from row #2, ExpDt is coming from row #3 and ExpTm is coming from row #4

                  If you understand this logic, then you will understand that you must find a rule to select SYSCODE and NAME.

                  I hope I was clear.

                  Regards.
                  Al

                  Edited by: Alberto Faenza on Oct 11, 2012 3:49 PM

                  Additional explanation added
                  1 2 Previous Next