CODEWITHSUNDEEP
sqloracle-database
D. Caan
D. Caan

Reputation: 2019

First and Last value ORACLE

Trying to get this output in Oracle 8i:

    column1 |time
    ________|_______
      ABC   | 00:00:01
      END   | 00:00:03
      123   | 00:00:04
      END   | 00:00:07

with this output from another query

   column1 |time
    ________|_______
      ABC   | 00:00:01
      ABC   | 00:00:02
      ABC   | 00:00:03
      123   | 00:00:04
      123   | 00:00:05
      123   | 00:00:06
      123   | 00:00:07

Is there any command for "first" or "last" that I could use here [like in XPath]? Tried to GROUP BY but it doesnt work fine :(

Thanks!

Upvotes: 1

Views: 137

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can put this in two columns, which would be the more nature approach:

select column1, min(time) as firsttime, max(time) as lasttime
from t
group by column1;

Do yo really want four rows of output, with END on two rows that don't identify what is ending?

EDIT:

To get the output you want:

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;

Note the order by clause. Results are not guaranteed to be in any particular order, and ordering is important for understanding these results.

Upvotes: 3

Related Questions