CODEWITHSUNDEEP
sqloracle-database
wlz
wlz

Reputation: 623

merge adjacent repeated rows into one

I want to merge adjacent repeated rows into one , for example , I have a table demo with two columns ,

data | order
-------------
 A   | 1 
 A   | 2
 B   | 3
 B   | 4
 A   | 5

I want the result to be :

A
B
A

How to achieve this by one select SQL query in oracle ?

Upvotes: 0

Views: 219

Answers (3)

Deepika Janiyani
Deepika Janiyani

Reputation: 1477

The answer suggested by Dmitry above is working in SQL, to make it work in oracle you need to do some modifications.

order is a reserved keyword you need to escape it as follows.

select 
    *
from 
    Table1 t1
where not exists(
    select * from Table1 t2 
  where 
      t2."order" = t1."order" - 1 
    and 
      t1."data" = t2."data"
) order by "order"

Working Fiddle at http://sqlfiddle.com/#!4/cc816/3

Upvotes: 2

user3278460
user3278460

Reputation:

please, try something like this 

select *
from table t1
where not exists(select * from table t2 where t2.order = t1.order - 1 and t1.data = t2.data)

Upvotes: 2

Xavjer
Xavjer

Reputation: 9226

You can group by a column

Take a look at http://docs.oracle.com/javadb/10.6.1.0/ref/rrefsqlj32654.html

Example from official oracle site:

SELECT AVG (flying_time), orig_airport
FROM Flights
GROUP BY orig_airport

Upvotes: -2

Related Questions