This discussion is archived
11 Replies Latest reply: Sep 10, 2013 7:52 AM by chris227 RSS

SQL query help

NM Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Do you want first column descending and second column ascending ?

     

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

    Hi,

     

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

  • 5. Re: SQL query help
    Kapil Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points