-1

I've got the following code [thanks to Gordon Linoff]:

   select (case when n.n = 1 then column1 else 'END' end) as column1,
           (case when n.n = 1 then firsttime else lasttime end) as "time"
    from (select column1, min(time) as firsttime, max(time) as lasttime
          from t
          group by column1
         ) t cross join
         (select 1 as n from dual union all select 2 from dual) n
    order by column1, n.n;

there are more columns in table "t" so I'm trying to bring them with the following query:

   select (case when n.n = 1 then column1 else 'END' end) as column1,
           (case when n.n = 1 then firsttime else lasttime end) as "time",
           column2, column3
    from (select column1,column2,column3, min(time) as firsttime, max(time) as lasttime
          from t
          group by column1
         ) t cross join
         (select 1 as n from dual union all select 2 from dual) n
    order by column1, n.n;

but "ORA-00979: not a GROUP BY expression" is the output.

D. Caan
  • 1,907
  • 6
  • 22
  • 36

1 Answers1

2

You need to add the additional columns to the group by as well:

select (case when n.n = 1 then column1 else 'END' end) as column1,
       (case when n.n = 1 then firsttime else lasttime end) as "time",
       column2, column3
from (select column1,column2,column3, min(time) as firsttime, max(time) as lasttime
      from t
      group by column1, column2, column3
     ) t cross join
     (select 1 as n from dual union all select 2 from dual) n
order by column1, n.n;

Alternatively, you could use aggregation functions in the select:

from (select column1, min(column2) as column2, min(column3) as column3, . . .
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786