Col 1- PK
Coll 1- FK coll 2 coll 3 coll 4 coll 5
1 5/28/2015 23:44 A1 X1 123
2 5/28/2015 23:45 NULL S1 10
2 5/28/2015 23:43 B1 S2 11
2 5/28/2015 23:42 B2 S3 12
2 5/28/2015 23:41 B3 S4 13
3 5/28/2015 23:45 X1 Q1 NULL
3 5/28/2015 23:43 X2 Q2 500
3 5/28/2015 23:42 X3 Q3 501
Result, I want
Col 1- PK coll 2 coll 3 coll 4 coll 5
1 A1 5/28/2015 23:44 X1 123
2 B1 5/28/2015 23:43 S4 10
3 X1 5/28/2015 23:42 Q3 500
I have to achieve a above result from 2 tables mention above, when I am using Qualify to filter out one record, some values are missing that are coming from different row but same PK. I tried using RANK also. But it didn't work out. ( If i will be dividing lookup table into muliple tables using Qulaify then I need to so many left outer join for each and every column - It will work out but its not looking good approach)
Can someone suggest a good approach that I can implement?
Thanks in Advance!!
Can you please explain the logic with which the Result shown above for Col 2?
for Other columns logic looks like either its Min or Max ignoring the Nulls.
Can you please check if the below SQL suits your need for them.
SEL COL 1_PK, MIN ( CASE WHEN COL3 IS NOT NULL THEN COL3 END ) AS COL3
, MAX( CASE WHEN COL4 IS NOT NULL THEN COL4 END) AS COL4
, MIN (CASE WHEN COL5 IS NOT NULL THEN COL5 END) AS COL5
FROM LOOKUP TABLE
INNER JOIN ON MAINTABLE
ON COL1_PK = COL1_FK
GROUP BY COL1_PK
col 1: Unique
Colll2: Min date-time or first date-time when the record arrived.
Coll3: the latest record that isn’t null for the required value.
Coll4: value which is against the Min date-time or when record 1st arrived.
Coll5: the latest record that isn’t null for the required value.
So on your result the value for col2 for 2ndrow should have been 5/28/2015 23:41 instead of 5/28/2015 23:43.
Coming to the solution, The Approach you mentioned above ( join tables for every column) is the viable solution in this case.
Had it been non-null columns you could have done it easily with 2 tables. one having all the fields based on Ascending time and one based on descending time qualifying the first row. and joining them based on ID.
Like this in another table I am having 25 columns. So for fetching 25 columns I need to put 25 Qualify statement and 25 left join with same table. This is not looking good to me. I tried to implement this with RANK and Qualify but didn't worked.
Use FIRST_VALUE,IGNORE NULLS....
FIRST_VALUE(A2.COL3 IGNORE NULLS) OVER (PARTITION BY A2.COL1 ORDER BY A2.COL2 DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )