11 Replies Latest reply: Sep 10, 2013 9:52 AM by chris227 RSS

    SQL query help

    NM

      Hi,

       

      I want to order by  participantid and totalvalue but P Column should be L and M

       

      {code}

      SELECT  participantid, ptype, numorders, numshares, totalvalue

          FROM (SELECT  ob.participantid participantid,

                        SUBSTR (p.shortdesc, 1, 1) ptype, COUNT (*) numorders,

                        SUM (quantity) numshares,

                        SUM (price * quantity * ex.exchangerate) totalvalue,

                        RANK () OVER (PARTITION BY ob.participantid, SUM(price* quantity* ex.exchangerate) ORDER BY ob.participantid,SUM (price * quantity * ex.exchangerate)) r

                    FROM tibex_orderbook

                        ob JOIN tibex_pricetypeenum p USING (pricetype)

                        JOIN tibex_instrument ti ON ob.instrumentid =

                                                                    ti.instrumentid

                        JOIN tibex_exchangerateview ex

                        ON ti.currencycode = ex.currencycode

                GROUP BY participantid, p.shortdesc

                )

      ORDER BY r;

       

      Current output

       

      PARTICIPANTID             P  NUMORDERS  NUMSHARES TOTALVALUE

      ------------------------- - ---------- ---------- ----------

      BBVA                      L         34      85452  729568202

      BDIR                      L          3          3         51

      BYLB                      L        376    1715941 1.7277E+10

      GLT1                      L          4        240      553.4

      GLT2                      L          1        150  2536519.5

      INGB                      L       6233      52549    97274.2

      INGB                      M          6         34

      KEYT                      L          1       1900      49894

      NITE                      L          1       1000       4000

      PROC                      L          3        603   17210.78

      PROC                      M          2        103

      SEBA                      L          1       2250     236250

      WINT                      L          6       9288    1245876

       

      Expected output

       

      PARTICIPANTID             P  NUMORDERS  NUMSHARES TOTALVALUE

      ------------------------- - ---------- ---------- ----------

      BYLB                      L        376    1715941 1.7277E+10

      BBVA                      L         34      85452  729568202

      GLT2                      L          1        150  2536519.5

      WINT                      L          6       9288    1245876

      SEBA                      L          1       2250     236250

      KEYT                      L          1       1900      49894

      INGB                      L       6233      52549    97274.2

      INGB                      M          6         34

      PROC                      L          3        603   17210.78

      PROC                      M          2        103

      NITE                      L          1       1000       4000

      GLT1                      L          4        240      553.4

      BDIR                      L          3          3         51

       

      {code}

       

      Regards

      NM

        • 1. Re: SQL query help
          Frank Kulash

          Hi,

           

           

           

          NM wrote:

           

          Hi,

           

          I want to order by  participantid and totalvalue but P Column should be L and M

          ...

          Are you saying that you want to order by participantid and totalvalue, but if 2 (or more) rows have the same combination of participantid and totalvalue, then rows among them iwth ptype='L'  should come before rows with ptype='M'?

          That's just another way of saying you want to order by participantid, totalvalue and ptype.  The way to code that is simply:

           

          ORDER BY  participantid

          ,         totalvalue

          ,         ptype

          In this particular example, that wouldn't change the part you highlighted (where participantid='INGB'), because those rows do not have the exact same values of totalvalue.  Perhaps you want to put ptype before totalvalue in the ORDER BY clause.  Also, if paticiopantid is the first thing you want to sort by, I don't see why you want 'BDIR' at the end of the list, far away from 'BYLB' and 'BBVA'..

           

          It never makes sense to PARTITION BY and ORDER BY the same thing in any analytic function.  "PARTITION BY x, y" means that only rows with the same exact values of x and y will be compared to one another.  Since they all have the exact same values of x and the exact smae values of y, "ORDER BY x, y" is guarranteed not to do anything.

           

          I hope this answers your question.
          If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Post a revised query, using the ORDER BY clause above.  Point out where that query is giving the wrong results, and explain, using specific examples, how you get the correct results from the given data in those places.

          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002

          {message:id=9360002}

          • 2. Re: SQL query help
            NM

            Hi Frank,

             

            {code}

             

            SELECT ob.PARTICIPANTID,

                   substr(p.shortDesc,1,1) T,

                   count(*) NUMORDERS,

                   sum (price*quantity*ex.exchangerate) TOTALVALUE

              FROM  tibex_orderBook ob

               JOIN  tibex_priceTypeEnum p USING (priceType)

               join tibex_instrument ti

                   on ob.instrumentid = ti.instrumentid

                   join tibex_exchangerateview ex

                    on ti.currencycode = ex.currencycode

              GROUP BY participantID, p.shortDesc

              ORDER BY 1,2;

             

             

            Current output

             

            Member  T  Orders      Shares    Total Value
            -------- - -------- ----------- ---------------
            ALET    L      375  3,270,220      4,673,010
            ALET    M        1        200
            BDIR    L  17,545  4,060,758      48,026,219
            BDIR    M    1,970    374,865
            INGB    L    2,498  3,392,089  1,128,739,650
            KBCS    L      850    353,591      6,705,658
            KBCS    M        7      10,027
            KEYT    L    2,197  1,529,391      18,773,107
            KEYT    M      199      79,954
            NATX    L    1,897    672,650      11,587,365
            NATX    M      84      28,840
            NITE    L  32,324  15,215,495  1,248,773,182
            NITE    M      73    103,068
            PROC    L  10,018  2,605,256      43,711,915
            PROC    M      70      29,512
            WINS    L    2,937    689,042      13,593,527
            WINS    M        8      1,437


            Expected output

             

            Member  T  Orders      Shares    Total Value
            -------- - -------- ----------- ---------------
            NITE    L 
            32,324  15,215,495  1,248,773,182
            NITE    M     
            73    103,068
            INGB    L   
            2,498  3,392,089  1,128,739,650
            BDIR    L 
            17,545  4,060,758      48,026,219
            BDIR    M   
            1,970    374,865
            PROC    L 
            10,018  2,605,256      43,711,915
            PROC    M     
            70      29,512
            KEYT    L   
            2,197  1,529,391      18,773,107
            KEYT    M     
            199      79,954
            WINS    L   
            2,937    689,042      13,593,527
            WINS    M       
            8      1,437
            NATX    L   
            1,897    672,650      11,587,365
            NATX    M     
            84      28,840
            KBCS    L     
            850    353,591      6,705,658
            KBCS    M       
            7      10,027
            ALET    L     
            375  3,270,220      4,673,010
            ALET    M       
            1        200
            Oracle version 11.2.0.2

            Regards

            NM

            • 3. Re: SQL query help
              Kapil

              Do you want first column descending and second column ascending ?

               

              order by 1 desc,2 asc
              
              • 4. Re: SQL query help
                NM

                Hi,

                 

                I want the each Member(both L ad M) with Highest Total value.

                • 5. Re: SQL query help
                  Kapil
                  select * from (
                  with t as (
                  select 'KBCS' member,    'L' t,      850,    353591 ,     6705658 total_value from dual union all
                  select 'KBCS',    'M',        7,      10027 ,null total_value from dual union all
                  select 'NITE' member,    'L' t,  32324 orders,  15215495 shares,  1248773182 total_value from dual union all
                  select 'NITE',    'M',      73,    103068,null   from dual union all
                  select 'INGB',    'L',    2498,  3392089,  1128739650 total_value from dual union all
                  select 'BDIR',    'L',  17545 , 4060758 ,     48026219 total_value from dual union all
                  select 'BDIR',    'M',    1970,    374865 ,null total_value from dual union all
                  select 'PROC',    'L',  10018 , 2605256  ,    43711915 total_value from dual union all
                  select 'PROC',    'M',      70,      29512,null  total_value from dual union all
                  select 'KEYT',    'L',    2197,  1529391 ,     18773107 total_value from dual union all
                  select 'KEYT',    'M',      199,      79954,null  total_value from dual union all
                  select 'WINS',    'L',    2937,    689042 ,     13593527 total_value from dual union all
                  select 'WINS',    'M',        8,      1437,null  total_value from dual union all
                  select 'NATX',    'L',    1897,    672650  ,    11587365 total_value from dual union all
                  select 'NATX',    'M',      84,      28840 ,null total_value from dual union all
                  select 'ALET',    'L',      375,  3270220  ,    4673010 total_value from dual union all
                  select 'ALET',    'M',        1,        200,null  total_value from dual)
                  select t.*,max(total_value) over (partition by member) max_val from t
                  )
                  order by max_val desc,member
                  
                  • 6. Re: SQL query help
                    NM

                    Hi Kapil,

                     

                    But the output will be changing so i cannot use the Hard coded values.

                     

                    {code}

                    Current output

                    PARTICIPANTID             P  NUMORDERS  NUMSHARES TOTALVALUE

                    ------------------------- - ---------- ---------- ----------

                    BBVA                      L         34      85452  729568202

                    BDIR                      L          3          3         51

                    BYLB                      L        376    1715941 1729568202

                    GLT1                      L          4        240      553.4

                    GLT2                      L          1        150  2536519.5

                    INGB                      L       6233      52549    97274.2

                    INGB                      M          6         34

                    KEYT                      L          1       1900      49894

                    NITE                      L          1       1000       4000

                    PROC                      L          3        603   17210.78

                    PROC                      M          2        103

                    SEBA                      L          1       2250     236250

                    WINT                      L          6       9288    1245876

                     

                    Expected output

                    PARTICIPANTID             P  NUMORDERS  NUMSHARES TOTALVALUE

                    ------------------------- - ---------- ---------- ----------

                    BYLB                      L        376    1715941 1729568202

                    BBVA                      L         34      85452  729568202

                    GLT2                      L          1        150  2536519.5

                    WINT                      L          6       9288    1245876

                    SEBA                      L          1       2250     236250

                    KEYT                      L          1       1900      49894

                    INGB                      L       6233      52549    97274.2

                    INGB                      M          6         34

                    PROC                      L          3        603   17210.78

                    PROC                      M          2        103

                    NITE                      L          1       1000       4000

                    GLT1                      L          4        240      553.4

                    BDIR                      L          3          3         51

                    {code}

                     

                    Regards

                    NM

                    • 7. Re: SQL query help
                      NM

                      Sample output

                       

                      {code}

                       

                      SET DEFINE OFF;

                      Insert into T

                         (MEMBER, T, "850", "353591", TOTAL_VALUE, MAX_VAL)

                      Values

                         ('NITE', 'L', 32324, 15215495, 1248773182, 1248773182);

                      Insert into T

                         (MEMBER, T, "850", "353591", MAX_VAL)

                      Values

                         ('NITE', 'M', 73, 103068, 1248773182);

                      Insert into T

                         (MEMBER, T, "850", "353591", TOTAL_VALUE, MAX_VAL)

                      Values

                         ('INGB', 'L', 2498, 3392089, 1128739650, 1128739650);

                      Insert into T

                         (MEMBER, T, "850", "353591", MAX_VAL)

                      Values

                         ('BDIR', 'M', 1970, 374865, 48026219);

                      Insert into T

                         (MEMBER, T, "850", "353591", TOTAL_VALUE, MAX_VAL)

                      Values

                         ('BDIR', 'L', 17545, 4060758, 48026219, 48026219);

                      Insert into T

                         (MEMBER, T, "850", "353591", MAX_VAL)

                      Values

                         ('PROC', 'M', 70, 29512, 43711915);

                      Insert into T

                         (MEMBER, T, "850", "353591", TOTAL_VALUE, MAX_VAL)

                      Values

                         ('PROC', 'L', 10018, 2605256, 43711915, 43711915);

                      Insert into T

                         (MEMBER, T, "850", "353591", MAX_VAL)

                      Values

                         ('KEYT', 'M', 199, 79954, 18773107);

                      Insert into T

                         (MEMBER, T, "850", "353591", TOTAL_VALUE, MAX_VAL)

                      Values

                         ('KEYT', 'L', 2197, 1529391, 18773107, 18773107);

                      Insert into T

                         (MEMBER, T, "850", "353591", MAX_VAL)

                      Values

                         ('WINS', 'M', 8, 1437, 13593527);

                      Insert into T

                         (MEMBER, T, "850", "353591", TOTAL_VALUE, MAX_VAL)

                      Values

                         ('WINS', 'L', 2937, 689042, 13593527, 13593527);

                      Insert into T

                         (MEMBER, T, "850", "353591", MAX_VAL)

                      Values

                         ('NATX', 'M', 84, 28840, 11587365);

                      Insert into T

                         (MEMBER, T, "850", "353591", TOTAL_VALUE, MAX_VAL)

                      Values

                         ('NATX', 'L', 1897, 672650, 11587365, 11587365);

                      Insert into T

                         (MEMBER, T, "850", "353591", MAX_VAL)

                      Values

                         ('KBCS', 'M', 7, 10027, 6705658);

                      Insert into T

                         (MEMBER, T, "850", "353591", TOTAL_VALUE, MAX_VAL)

                      Values

                         ('KBCS', 'L', 850, 353591, 6705658, 6705658);

                      Insert into T

                         (MEMBER, T, "850", "353591", TOTAL_VALUE, MAX_VAL)

                      Values

                         ('ALET', 'L', 375, 3270220, 4673010, 4673010);

                      Insert into T

                         (MEMBER, T, "850", "353591", MAX_VAL)

                      Values

                         ('ALET', 'M', 1, 200, 4673010);

                      COMMIT;

                       

                       

                      Current output

                      PARTICIPANTID             P  NUMORDERS  NUMSHARES TOTALVALUE

                      ------------------------- - ---------- ---------- ----------

                      BBVA                      L         34      85452  729568202

                      BDIR                      L          3          3         51

                      BYLB                      L        376    1715941 1729568202

                      GLT1                      L          4        240      553.4

                      GLT2                      L          1        150  2536519.5

                      INGB                      L       6233      52549    97274.2

                      INGB                      M          6         34

                      KEYT                      L          1       1900      49894

                      NITE                      L          1       1000       4000

                      PROC                      L          3        603   17210.78

                      PROC                      M          2        103

                      SEBA                      L          1       2250     236250

                      WINT                      L          6       9288    1245876

                       

                      Expected output

                      PARTICIPANTID             P  NUMORDERS  NUMSHARES TOTALVALUE

                      ------------------------- - ---------- ---------- ----------

                      BYLB                      L        376    1715941 1729568202

                      BBVA                      L         34      85452  729568202

                      GLT2                      L          1        150  2536519.5

                      WINT                      L          6       9288    1245876

                      SEBA                      L          1       2250     236250

                      KEYT                      L          1       1900      49894

                      INGB                      L       6233      52549    97274.2

                      INGB                      M          6         34

                      PROC                      L          3        603   17210.78

                      PROC                      M          2        103

                      NITE                      L          1       1000       4000

                      GLT1                      L          4        240      553.4

                      BDIR                      L          3          3         51

                       

                      {code}

                      • 8. Re: SQL query help
                        Frank Kulash

                        Hi,

                         

                        Thanks for postiong the INSERT statements for the sample data.  Don;t forget to post a CREATE TABLE statement, too.

                        Don't use names that require double-quotes; they will cause lots of confusion and problems.  Do you really need columns called "850" and "353591", especially whn you want the output to say NUMORDERS and NUMSHARES?  Even if "850" really is a more meaningful name for you than NUMORDERS, it would be better to call the column C_850, so you wouldn't need to double-auote it.

                         

                        Looking at your sample data, it appears that member='NITE" has the largest max_val, so I was expecting to see NITE at the beginnng of the expected output.  Why don't you want NITE in the output at all?  Are you sure the output you posted is what you expect from the sample data you psoted?

                        • 9. Re: SQL query help
                          chris227

                          SELECT  participantid, ptype, numorders, numshares, totalvalue

                              FROM (
                                 SELECT  ob.participantid participantid,

                                            SUBSTR (p.shortdesc, 1, 1) ptype
                                          , COUNT (*) numorders,

                                            SUM (quantity) numshares,

                                            SUM (price * quantity * ex.exchangerate) totalvalue,
                                           
                                            max(SUM (price * quantity * ex.exchangerate)) over (partition by ob.participantid) rv

                                        FROM tibex_orderbook

                                            ob JOIN tibex_pricetypeenum p USING (pricetype)

                                            JOIN tibex_instrument ti ON ob.instrumentid =

                                                                                        ti.instrumentid

                                            JOIN tibex_exchangerateview ex

                                            ON ti.currencycode = ex.currencycode

                                    GROUP BY participantid, p.shortdesc

                                    )

                          ORDER BY rv desc nulls last, participantid, ptype;

                           

                          Message was edited by: chris227 desc nulls last added

                          • 10. Re: SQL query help
                            NM

                            Hi Chris,

                             

                            Thanks for your help.I have another problem here if we have null or blank values the data comes to top rather then going to last.

                             

                            Current output

                            Member  T  Orders  SharesTotal Value

                            -------- - -------- ----------- ---------------

                            ALET375  3,270,220
                            BDIRL  17,545  4,060,758  48,026,219

                             

                            Expected.

                            Member  T  Orders  SharesTotal Value

                            -------- - -------- ----------- ---------------

                            BDIRL  17,545  4,060,758  48,026,219
                            ALET375  3,270,220
                            • 11. Re: SQL query help
                              chris227

                              See my query above. You can extend the order by with nulls last (which is the default for asc (which itself is the default for order by ;-) or nulls first (which is the default for desc (not really but that's the way i can remember it)

                               

                              So always consider to use nulls last when you apply desc and nulls values arfe possible

                              For example

                              ORDER BY rv desc nulls last