I need a help in writing a query.
The output I need is as below:
If the Position of a record is 1 (there can be multiple records with POS=1) then get me the minimum of datekey in column prin_date_key with an indicator of 'P' and init_date_key left as blank/null
In the init_date_key i need the min of date for all records no matter what position is. So in this case min date key is for POS=10 and that value should be in init_date_key
Currently I m using UNION ALL to achieve this but was curious if we can achieve this by doing a single pass on TABLEA as UNION and SELF join would require 2 passes on this table. Any thought???
Its ok if I dont have Position in my output. I can use the indicator.
In this case the union might be best. Otherwise you would need a small product join to duplicate at least the Position 1 rows into two rows.