CODEWITHSUNDEEP
sqloracle-database
cbm64
cbm64

Reputation: 1119

DeNormalize a table

I have a two table query (Oracle), lets call them A and B.

A

ID    SEQ
----  ----
1234  1
1235  3

B
ID    TYPE   SEQ  NUM
----  ----   ---  ---
1234  ORDER  1    10
1234  MODIFY 1    10
1235  ORDER  1    10
1235  MODIFY 1    10
1235  ORDER  2    11
1235  MODIFY 2    21
1235  ORDER  3    16

So Table A contains a unique row that has a SEQ number on it that is incremented every time something changes on Table B (Table B acts list a history table). So in the case on ID 1234 nothing has changed since rows were written so SEQ is 1 and both rows on Table B have a SEQ of 1.

However, ID 1235 has had three changes since it was first written. But in the third change only the TYPE of ORDER was changed. What I would like to do is represent both IDs with a denormalized result set. Something like a pivot.

So, if I were to do something like;

SELECT
A.ID
, MIN (CASE WHEN B.TYPE = 'ORDER'  THEN NUM END) AS V_ORDER
, MIN (CASE WHEN B.TYPE = 'MODIFY'  THEN NUM END) AS V_MODIFY
FROM A
, LEFT JOIN B ON B.ID = A.ID
AND B.SEQ = A.SEQ
GROUP BY A.ID

I would only get the results where the SEQ matched.

ID    V_ORDER  V_MODIFY
----  -------  -------
1234  10       10
1235  16       NULL

What I really want is to see the result from the maximum SEQ value;

ID    V_ORDER  V_MODIFY
----  -------  -------
1234  10       10
1235  16       21

Any thoughts?

Thanks C

Upvotes: 0

Views: 165

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

Join to a derived table that selects the max value. Here is a generic example.

select fred, barney, pebbles, wilma
from flinstones join
(select fred, max(wilma) maxwilma
from flinstones
were whatever
group by fred) bedrock using (fred)
where whatever
and wilma = maxwilma

Upvotes: 1

Related Questions