SQL & PL/SQL

What is SortKey?

select A,B,A1,B1
from (select A,B,
      Lead(A) over(order by SortKey) as A1,
      Lead(B) over(order by SortKey) as B1
      from YourTable)
where A1 is not null;

There is no sortkey. The output of query should follow the order in which they occur in the original table. Just you have to go on merging the consecutive rows of the original table in the given order.

Hope you got my point. Thanks for the prompt reply.

I see.
SortKey is none.

Therefore,you may use this query.

create table YourTable as select 1 as A,4 as B from dual
union select 9,7 from dual
union select 3,2 from dual
union select 7,9 from dual;

select A,B,A1,B1
from (select A,B,
      Lead(A) over(order by RowNum) as A1,
      Lead(B) over(order by RowNum) as B1
      from YourTable)
where A1 is not null;

A  B  A1  B1
-  -  --  --
1  4   3   2
3  2   7   9
7  9   9   7

Thanks a lot Aketi.. Its working.. But I have one more request..

As far as i know the lead is an oracle analytic and rownum is also specific to oracle db.
Is it possible to write a query for this using normal SQL so that my code doesnt get fixed to oracle.

As far as I could think to this, the below is the solution which i could get for this using JOIN operation, but it requires that there is an extra column of SRL_NO which specifies the ordering of rows in initial table.

____________
A | B|SRL_NO
---------------------
1 | 4| 1
9 | 7| 2
3 | 2| 3
7 | 9| 4

then using this query
SELECT
x.A , x.B , y.A AS A1 , y.B AS B1
FROM YOURTABLE x,YOURTABLE y
WHERE y.SRL_NO=x.SRL_NO+1;

I am able to get the required output which is

A B A1 B1
- - -- --
1 4 3 2
3 2 7 9
7 9 9 7

BUT i dont have SRL_NO column in my original table. I posted this solution just thinking that it may be helpful to you.

Looking forward from you to give a solution with normal sql queries which is database independent, if possible. Thanks

This is the solution which i could think of without using rownum or analytics

SELECT x.A , x.B, y.A AS A1, y.B AS B1
FROM
(SELECT A,B,ROWNUM AS SRL_NO FROM
(SELECT a.A,a.B FROM YOURTABLE a )) x,
(SELECT A,B,ROWNUM AS SRL_NO FROM
(SELECT a.A,a.B FROM YOURTABLE a )) y
WHERE y.SRL_NO = x.SRL_NO + 1;

Any comments on this solution on their performance or any other comments? Can you suggest any better solution. As it seems to me this query is very inefficient for large tables.

What is the order occured in the source table ?
Without sort, Oracle doesn't know in what order it should returned rows, and does not give any clue about the order. That is what we could call a pseudo-random output.

Oracle® Database SQL Reference
10g Release 2 (10.2)
Part Number B14200-02
SELECT:
Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

Nicolas.

ok actually the sort key is column B, The sample I posted in my first post is wrong as B is not sorted, the corrected sample is
A | B
-------
1 | 2
9 | 4
3 | 7
7 | 9
with B being in increasing order. For this data set the solution given Aketi with using B as sortkey i.e.

SELECT A,B,A1,B1
FROM (SELECT A,B,
Lead(A) over(ORDER BY B) AS A1,
Lead(B) over(ORDER BY B) AS B1
FROM YOURTABLE)
WHERE A1 IS NOT NULL;

is working... and also my solution for this problem without using analytics i.e.

SELECT x.A , x.B, y.A AS A1, y.B AS B1
FROM
(SELECT A,B,ROWNUM AS SRL_NO FROM
(SELECT a.A,a.B FROM YOURTABLE a ORDER BY B)) x,
(SELECT A,B,ROWNUM AS SRL_NO FROM
(SELECT a.A,a.B FROM YOURTABLE a ORDER BY B)) y
WHERE y.SRL_NO = x.SRL_NO + 1;

is also working. But I feel the solution which I gave is not good for large databases. So can any one suggest other solution for this problem without using analytics?

What's wrong with analytic function ? They are available since Oracle 8i Entreprise Edition, and that should give one of the best answer.

Nicolas.

My concern of worry is that if I use analytics then my sql queries will become oracle dependent. In future in case if we plan of shifting to any other database then we will have to rewrite all these queries. That is why I am asking for good solution without using analytics

The Big Lie (tm) - all RDBMS products are alike and one can design and write efficient and scalable solutions that are RDBMS independent.

That is pure bs.

Only the truly ignorant developer (maybe even just plain stupid) treat the RDBMS as black box bit buckets which are all alike.

Why? Because one RDBMS is very DIFFERENT from another RDBMS. Because one RDBMS will differ in how it implements concurrency from another. Because one RDBMS will differ in transaction isolation levels from another. Because one RDBMS will have very different core features from another.

To design and develop an efficient, performing and scalable solution, the RDBMS MUST be the core of the solution.

Data is forever. Application code come.. and application code go. Like a spreadsheet program. But the spreadsheet's data is what is core.

And the very worse mistake a developer/architect/analyst/whatever can make, is to confuse the application with the data.

I don't think it is your concern to use analytics rather than generic sql. Your employer paid Oracle to have the features/performance for the price. I don't see it's common for a company to switch from one DB to another either. Before they can do that, they have to evaluate the impact and cost of doing so.
From the end user point of view, would they bother with what's behind the tools. But they will be bothered with a qurey return in 1 sec or 30 sec.

If SortKey is B and we use DB2 or SQLServer2005,
we can emulate Lag and Lead.

for instance

create table LagLeadTable as
select 'AAAA' as Code,100 as SortKey,5 as Val from dual
union select 'AAAA',200,20 from dual
union select 'AAAA',300,15 from dual
union select 'AAAA',400,30 from dual
union select 'BBBB',500,60 from dual
union select 'BBBB',600,70 from dual
union select 'BBBB',610,90 from dual
union select 'BBBB',620,50 from dual
union select 'BBBB',630,70 from dual
union select 'CCCC',100,80 from dual;

select Code,SortKey,
Lag(Val,3,0)  over(partition by Code order by SortKey) as Lag3,
Lag(Val,2,0)  over(partition by Code order by SortKey) as Lag2,
Lag(Val,1,0)  over(partition by Code order by SortKey) as Lag1,
Val,
Lead(Val,1,0) over(partition by Code order by SortKey) as Lead1,
Lead(Val,2,0) over(partition by Code order by SortKey) as Lead2,
Lead(Val,3,0) over(partition by Code order by SortKey) as Lead3
  from LagLeadTable;

Above query can be emulated by below query.

select Code,sortkey,
coalesce(max(Val) over(partition by Code order by SortKey
                  Rows between 3 preceding and 3 preceding),0) as Lag3,
coalesce(max(Val) over(partition by Code order by SortKey
                  Rows between 2 preceding and 2 preceding),0) as Lag2,
coalesce(max(Val) over(partition by Code order by SortKey
                  Rows between 1 preceding and 1 preceding),0) as Lag1,
Val,
coalesce(max(Val) over(partition by Code order by SortKey
                  Rows between 1 following and 1 following),0) as Lead1,
coalesce(max(Val) over(partition by Code order by SortKey
                  Rows between 2 following and 2 following),0) as Lead2,
coalesce(max(Val) over(partition by Code order by SortKey
                  Rows between 3 following and 3 following),0) as Lead3
  from LagLeadTable;

my site :-)
http://www.geocities.jp/oraclesqlpuzzle/8-13.html

If we will use MySQL and SortKey is unique,
we should use inner join using derived Rank.

for instance

create table YourTable as select 1 as A,4 as B from dual
union select 9,7 from dual
union select 3,2 from dual
union select 7,9 from dual;

select a.A,a.B,b.A as A1,b.B as B1
  from (select A,B,(select count(*)+1 from YourTable bb where bb.b< aa.b) as Rank
          from YourTable aa) a,
       (select A,B,(select count(*)+1 from YourTable bb where bb.b< aa.b) as Rank
          from YourTable aa) b
 where a.Rank+1 = b.Rank
order by a.B;

A  B  A1  B1
-  -  --  --
3  2   1   4
1  4   9   7
9  7   7   9

Thanks a Lot all of you guyz for giving me such good suggestions. Also thanks for giving the alternative queries in different databases. Now I willl safely use the analytics in my code.

Hi I have a one more problem.. Actually I have a modification in my format. Now lets consider the format of

ID| A | B
------------
1 |1 | 2
1 |9 | 4
1 |3 | 7
1 |7 | 9
2 |1 | 3
2 |6 | 5
2 |3 | 9
2 |8 | 12

where there is an addition of column ID at the begining.. The question remains the same like merging up consecutive rows BUT only within the groups of ID.. i.e. I want to merge consecutive rows of all which are starting with ID 1 and then I want to merge rows which start with ID 2. So the expected output is

ID|A|B|A1|B1
-------------------
1 | 1 | 2 | 9 | 4
1 | 9 | 4 | 3 | 7
1 | 3 | 7 | 7 | 9
2 | 1 | 3 | 6 | 5
2 | 6 | 5 | 3 | 9
2 | 3 | 9 | 8 | 12

so i had modified the query given by Aketi in which I used the SortKey instead of only B, I replaced it with ID, B as SortKey. BUT then the output which I am getting is

ID|A|B|A1|B1
-------------------
1 | 1 | 2 | 9 | 4
1 | 9 | 4 | 3 | 7
1 | 3 | 7 | 7 | 9
1 | 7 | 9 | 1 | 3 <------- unncecsary entry in output.. because of wrong condition i.e. A1 is not null is not working here
2 | 1 | 3 | 6 | 5
2 | 6 | 5 | 3 | 9
2 | 3 | 9 | 8 | 12

As you can see there is an extra entry in 4th row which should not be there. The reason is because we used the condition where A1 is not null which will not work here..

The modified query of Aketi which I have used where I modified the sortkey is
SELECT ID,A,B,A1,B1
FROM (SELECT ID,A,B,
Lead(A) over(ORDER BY ID,B) AS A1,
Lead(B) over(ORDER BY ID,B) AS B1
FROM YOURTABLE ORDER BY ID,B)
WHERE A1 IS NOT NULL ORDER BY ID,B;
which gave the erronous result...

As it seems the condition A1 IS NOT NULL should be modified.. But can you suggest to what should it be?

It is easy that you can get ResultSet which you want.

SELECT ID,A,B,A1,B1
FROM (SELECT ID,A,B,
      Lead(A) over(partition by ID ORDER BY ID,B) AS A1,
      Lead(B) over(partition by ID ORDER BY ID,B) AS B1
      FROM YOURTABLE)
WHERE A1 IS NOT NULL
ORDER BY ID,B;

Thanks A lot Aekti again. That exactly solved my problem