2 Replies Latest reply on Jul 17, 2013 10:40 AM by swapnil kambli

    Need help with order by

    swapnil kambli


      Test Data:

       

        CREATE TABLE "TEST_GMU"

         ( "PZINSKEY" VARCHAR2(255) NOT NULL ENABLE,

      "PXCREATEDATETIME" DATE,

      "PXURGENCYASSIGN" NUMBER(18,0),

      "WORK_PXURGENCYWORK" NUMBER(18,0),

      "MASTERACCNTFIRMCUSTID" VARCHAR2(255 CHAR)

         ) SEGMENT CREATION IMMEDIATE

        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

       

      insert into test_gmu values ('ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12858!AMADVISORSERVICESFLOW','16-JUL-13 15.55.57.000000 PM',0,40,'2531215'); 

      insert into test_gmu values ('ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12859!AMADVISORSERVICESFLOW','16-JUL-13 15.01.22.000000 PM',0,40,'742254777');

      insert into test_gmu values ('ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12860!AMADVISORSERVICESFLOW','16-JUL-13 15.01.23.000000 PM',0,40,'2531215'); 

      insert into test_gmu values ('ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12861!AMADVISORSERVICESFLOW','16-JUL-13 15.03.55.000000 PM',0,40,'2568091'); 

      insert into test_gmu values ('ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12862!AMADVISORSERVICESFLOW','16-JUL-13 15.03.56.000000 PM',0,40,'742254777');

      insert into test_gmu values ('ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12863!AMADVISORSERVICESFLOW','16-JUL-13 15.03.57.000000 PM',0,40,'2568091'); 

      insert into test_gmu values ('ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12864!AMADVISORSERVICESFLOW','16-JUL-13 15.06.29.000000 PM',0,40,'742254777');

      insert into test_gmu values ('ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12865!AMADVISORSERVICESFLOW','16-JUL-13 15.06.31.000000 PM',0,40,'2568091'); 

      insert into test_gmu values ('ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12866!AMADVISORSERVICESFLOW','16-JUL-13 15.06.32.000000 PM',0,40,'742254777');

       

      The output required is like below

       

      1.       AM-12859 BXYZAB CO. 742254777  07/16/2013 15:01:21

      2.       AM-12862 BXYZAB CO. 742254777  07/16/2013 15:03:56

      3.       AM-12864 BXYZAB CO. 742254777  07/16/2013 15:06:27

      4.       AM-12866 BXYZAB CO. 742254777  07/16/2013 15:06:31

      5.       AM-12858 WHIJKL CO.  2531215  07/16/2013 15:01:16       The values of this timestamp is actually '16-JUL-13 15.55.57 in datbase

      6.       AM-12860 WHIJKL CO.  2531215  07/16/2013 15:01:22

      7.       AM-12861 SIJKLM CO.  2568091  07/16/2013 15:03:54

      8.       AM-12863 SIJKLM CO.  2568091  07/16/2013 15:03:56

      9.       AM-12865 SIJKLM CO.  2568091  07/16/2013 15:06:30

       

      I could work this out till below:

       

      ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12859!AMADVISORSERVICESFLOW 7/16/2013 3:01:22 PM 0 40 742254777
      ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12862!AMADVISORSERVICESFLOW 7/16/2013 3:03:56 PM 0 40 742254777
      ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12864!AMADVISORSERVICESFLOW 7/16/2013 3:06:29 PM 0 40 742254777
      ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12866!AMADVISORSERVICESFLOW 7/16/2013 3:06:32 PM 0 40 742254777
      ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12861!AMADVISORSERVICESFLOW 7/16/2013 3:03:55 PM 0 40 2568091  
      ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12863!AMADVISORSERVICESFLOW 7/16/2013 3:03:57 PM 0 40 2568091  
      ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12865!AMADVISORSERVICESFLOW 7/16/2013 3:06:31 PM 0 40 2568091  
      ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12860!AMADVISORSERVICESFLOW 7/16/2013 3:01:23 PM 0 40 2531215  
      ASSIGN-WORKBASKET SCHWAB-ACE-SERVICEREQUEST-WORK-ACCTMAINT AM-12858!AMADVISORSERVICESFLOW 7/16/2013 3:55:57 PM 0 40 2531215 
      

       

      with order by masteraccntfirmcustid desc ,pxcreatedatetime

      But one of the developer told me masteraccntfirmcustid should be asc

      This resultset is supposed to be ordered by

       

      order by masteraccntfirmcustid ,pxurgencyassign desc NULLS LAST ,work_pxurgencywork desc NULLS LAST,pxcreatedatetime

       

      in the test data I have been given pxurgencyassign and work_pxurgencywork is same for all the records

       

      Could you please help?

       

      Thanks,

      swapnil