Oracle 11g: only return a value if row is first or last in group

Not tested, but this should be the idea. If you need to sort by COUNT_1 or COUNT_2, you should include it in the analytic functions' over clause, partition by GROUP_ID order by COUNT_1

Refer here to understand what an analytic function is.

select  case when ROW_NUMBER = 1 then GROUP_ID       when ROW_NUMBER = GROUP_COUNT then 'last'       else NULL  end GROUP_ID  ,COUNT_1  ,COUNT_2from(    select      GROUP_ID      ,COUNT_1      ,COUNT_2      ,row_number() over(partition by GROUP_ID) ROWNUMBER      ,count(GROUP_ID) over (partition by GROUP_ID) GROUP_COUNT    from      FOO)

CREATE TABLE tt(g NUMBER, c1 NUMBER, c2 NUMBER);INSERT INTO tt VALUES(1, 100, 123);INSERT INTO tt VALUES(1, 101, 123);INSERT INTO tt VALUES(1, 283, 342);INSERT INTO tt VALUES(1, 134, 123);INSERT INTO tt VALUES(2, 241, 432);INSERT INTO tt VALUES(2, 321, 920);INSERT INTO tt VALUES(2, 432, 121);INSERT INTO tt VALUES(2, 135, 342);SELECT CASE WHEN 1=ROW_NUMBER() OVER (PARTITION BY g ORDER BY c1 ASC,  c2 ASC) THEN '1'            WHEN 1=ROW_NUMBER() OVER (PARTITION BY g ORDER BY c1 DESC, c2 DESC) THEN 'Last'            ELSE 'Empty'        END answer,       c1, c2  FROM tt;1        100  123Empty    101  123Empty    134  123Last     283  3421        135  342Empty    241  432Empty    321  920Last     432  121

You can try using the lag() and lead() analytical functions:

with a as (   select 1 group_id, 100 count_1, 123 count_2 from dual union all   select 1 group_id, 101 count_1, 123 count_2 from dual union all   select 1 group_id, 283 count_1, 342 count_2 from dual union all   select 1 group_id, 134 count_1, 123 count_2 from dual union all   select 2 group_id, 241 count_1, 432 count_2 from dual union all   select 2 group_id, 321 count_1, 920 count_2 from dual union all   select 2 group_id, 432 count_1, 121 count_2 from dual union all   select 2 group_id, 135 count_1, 342 count_2 from dual )select  case lag (group_id) over (order by group_id, count_1)     when group_id then       case lead (group_id) over (order by group_id, count_1)      when group_id then null           else 'last'      end    else to_char(group_id) end x,  count_1,  count_2froma;

with lag(group_id) over (order by group_id, count_1) you get the group_id of the previous record (it lags behind). Similarly, with lead(group_id) over... you get the group_id of the next record.

With the case expression, you compare the current group_id with the next and previous one and then return the appropriate value.