CODEWITHSUNDEEP
sqloracle11g
user3422637
user3422637

Reputation: 4229

Search for a value of a column in another column across the rows in SQL using analytic functions?

Here's how my data looks like:

ID  month  desired_month val
1   6      4             101
1   4      6             102
2   12     4             103
2   4      3             104
2   3      12            105

I want look across all rows for the same ID and find the row which has month equal to the desired_month of the row in question and then return the val from the row that matched.

Resultant dataset would look like this:

ID  month  desired_month val  val_from_row_that_matched_desired_month
1   6      4             101  102
1   4      6             102  101
2   12     4             103  104
2   4      3             104  105
2   3      12            105  103

I would like to achieve this using analytic functions in Oracle SQL

Upvotes: 0

Views: 43

Answers (3)

vivek das
vivek das

Reputation: 1

MAY BE ANSWER SHOULD BE LIKE THIS:-

SELECT E.ID,E.MONTH,M.DESIRED_MONTH, E.VAL,M.VAL AS "val_matched_desired_month" FROM ID_MONTH_DESIRED_MON_VAL E,ID_MONTH_DESIRED_MON_VAL M WHERE E.ID =M.DESIRED_MONTH ORDER BY ID

Upvotes: 0

ramana_k
ramana_k

Reputation: 1933

Assuming there is just one and only one row with desired month matching each (ID, month) combination in the data

select 
  a.id, a.month, 
  a.desired_month,
  a.val, b.val as val_from_desired_month
from input a, input b
where
  a.id = b.id
  and a.desired_month = b.month

Upvotes: 1

Sunil
Sunil

Reputation: 447

you can get desire output by using self join into same table like this.let suppose your table name is tab.

select t1.*,t2.val from tab t1
inner join tab t2
on(t1.desired_month=t2.month);

Upvotes: 1

Related Questions