need help on query...

j_rymbei · Posted: Thu Dec 14, 2006 12:47 pm

hi all,
I have a table say table1 having column (x,y and z) and data as below.
x y z
----------------------------
a b c
a1 b1 c1
a1 b1 c1
a1 b1 c1
a1 b1 c12
a2 b2 c2
How can I get all the rows marked bold above. i.e where prev value of all the columns (x, y,z) is equal to the next value of of column (x,y,z)

Rajen · New User Joined: 05 May 2005 Posts: 61

Hi j_rymbei
From your test data it seems like in the desired result you can have 3 rows of the same value.

Here
a1 b1 c1
a1 b1 c1
a1 b1 c1 all 3 rows are of same value.

And one more thing instead of a1 b1 c1 and a2 b2 c2, can you please give us some real data. that would be very helpful.

Thanks,
Rajen Patel.

j_rymbei · Posted: Thu Dec 14, 2006 2:11 pm

Yes they are of same value..I have found the solution to get the data..
just to share with you..might be useful..
select x,y,z count (*) from table1
group by x,y,z
having count (*) > 1;

ovreddy · Posted: Mon Dec 18, 2006 7:05 pm

Hi,

One small correction to the above query...

select x,y,z, count (*) from table1
group by x,y,z
having count (*) = 3 ;

This will fetch the rows which have identical data in 3 rows but may not be in sequence. If you want them to be in sequence then we need to use program logic.

Bye,
Reddy