Question

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

edited Jan 7 at 22:32

Carlos A. Ibarra♦♦
78●5

asked Jan 6 at 7:48

TDHELP
5●1

Carlos A. Ibarra · Answer 1 · 2010-01-07 22:50:48Z UTC

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);

Babak · Answer 2 · 2010-01-06 08:49:01Z UTC

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

order by issues

2 Answers

Your Answer

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

Welcome to TeradataQuestions

Useful links