0

Hi,

I need help in order by, I have a subquery which gets this data.

pname version   count
xxx    V12.1        1
yyy    V22.1.3      1

We are using grouping function and roll up in the main query, the query was written in the following way: 

SELECT case when grouping(pname) =  1 then 'Total' 
       else 
          case when grouping(version) = 1 then 'Subtotal ' || pname
      when RANK() over (partition by pname order by SUM(count)) = 1 then pname
      else ' ' 
          end 
       end "NAME", 
version "VERSION", SUM(count) "Qty" 
FROM (
       SELECT pname, version, 1 count from tablea, tableb, tablec
       where tablea.key = tableb.key
       and tableb.key = tablec.key
       --order by pname, version   (ghis order by worked in oracle , but does not work in TD because order by does not work on subquery)
      )as derived_table GROUP BY rollup (pname,version)

With this query, the resultset comes in the following way:

NAME                   VERSION    QTY
Total                     ?	2
Subtotal XXX             ?	1
XXX               V12.1	1
YYY               V22.1.3	1
Subtotal YYY             ?	1

When compared to oracle the data looks good, but the records are not ordering properly. The actual output should come like this:

NAME                   VERSION    QTY
XXX               V12.1	1
Subtotal XXX             ?	1
YYY               V22.1.3	1
Subtotal YYY             ?	1
Total                     ?	2

Could anyone help me to display the data in this order. I appreciate your help. Thanks in advance.

Regards, TDHELP

flag

2 Answers

1

You can't use count as a column name because it's a reserved word. I changed it to c and simplified your query a bit.

Here is how you can get your desired ordering:

SELECT  
   case
      when grouping(pname) =  1 then 'Total'  
      when grouping(version) = 1 then 'Subtotal ' || pname
      else   pname 
     end    "NAME", version "VERSION", SUM(c) "Qty" 
FROM    (       
SELECT  pname, version, 1 c
from    tt
 )as derived_table GROUP BY rollup (pname,version)
 order by grouping(pname),pname,grouping(version);
link|flag
This worked. Thanks a lot. Appreciate your help. – TDHELP Jan 8 at 22:43
Then please accept the answer by clicking the checkmark icon next to it. – Carlos A. Ibarra♦ Jan 9 at 23:22
0

try this:

SELECT case when grouping(pname) = 1 then 'Total' else case when grouping(version) = 1 then 'Subtotal ' || pname when RANK() over (partition by pname order by SUM(count)) = 1 then pname else ' ' end end "NAME", version "VERSION", SUM(count) "Qty" FROM ( SELECT pname, version, 1 count from tablea, tableb, tablec where tablea.key = tableb.key and tableb.key = tablec.key )as derived_table GROUP BY rollup (pname,version) order by pname, version

link|flag
Hi, Thanks for the reply. It did not work the order I needed. In the query instead of 'Total' please add 'Grand Total'. The Pname order should come in the following way: XXX, Subtotal XXX, YYY, Subtotal YYY, Grand Total Currently it is coming in this way after adding this 'ORDER BY PNAME,VERSION desc': GRAND Total, XXX, Subtotal XXX, YYY, Subtotal YYY. I want Grand Total to come at end Thanks and Regards, TDHelp – TDHELP Jan 6 at 18:55

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.