Help to frame select 1

[Shantha]

Hi,

The data residing in a particular table is of the following format:

ROW_VALUE FIELD_VALUE
------------- --------------------
1 1
1 BCKUP
2 13
2 BUSER
3 4
3 AUSER
4 36
4 CUB
5 7
5 BNMP
6 3

ROW_VALUE FIELD_VALUE
------------- --------------------
6 VIION
7 222
7 DDA
8 808
8 DWR
9 2
9 EREP
10 3
10 IM
11 3
11 MDBV

the field row_value unites the records meaning,
for field_value BCKUP = 1 (previous record), BUSER=13....

The previous record value available in the same field is the value to be matched for this record.

The data is stored in the table in the above format.

Help required to have a single select query to fetch the data from the above pattern.

Thanks,
Shantha.

Talent is what you possess;
genius is what possesses you

 

[Dagon]

How do you actually know which of the two values is the "previous" one ? For example, how do you know that the previous value in 1/BCKUP is BCKUP rather than 1 ? It doesn't seem to be that one is alphabetic whereas the other is numeric because you also have 3/IM later in the table and I assume that the "previous" value there will be 3.

If you are simply relying on the order in which rows are returned by the query, this is fundamentally wrong as you cannot guarantee what order Oracle will return the rows in. You need to add an extra column to distinguish between current and previous value.

 

[Dagon]

I misread the data there. 3/IM is in the same order as 1/BCKUP. So is the way of distinguish the previous record from the current that one is numeric and the other alphabetic ?

 

[SantaMufasa]

Shantha,

As you are probably aware, one must presume that data in an Oracle table is physically unordered. Therefore, if there is a requirement to have the data in a particular sequence, then one must use the SQL order by clause.

Since you did not post how you want the result set to appear, we must presume that you want the ROW_VALUE, NUMERIC_VALUE, and TEXT_VALUE for each ROW_VALUE pair, right? If so, here is code to achieve that result:

select ROW_VALUE,min(field_value) num_value, max(field_value) text_value
from shantha
group by row_value
order by row_value;

 ROW_VALUE NUM_VALUE  TEXT_VALUE
---------- ---------- ----------
         1 1          BCKUP
         2 13         BUSER
         3 4          AUSER
         4 36         CUB
         5 7          BNMP
         6 3          VIION
         7 222        DDA
         8 808        DWR
         9 2          EREP
        10 3          IM
        11 3          MDBV

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Shantha]

Hi SantaMufs,

This is what i exactly need...Thanks and please have a star.

Thanks,
Shantha.



Talent is what you possess;
genius is what possesses you