SQL Query - Help

Database
Enthusiast

SQL Query - Help

Hi,

 I need a help in writing a query.

TABLEA






Tbl_Key Position Date_Key
5148005 1 20101214
5148005 2 20101214
5148005 3 20101221
5148005 4 20101221
5148005 5 20101221
5148005 6 20101214
5148005 7 20101214
5148005 8 20101221
5148005 9 20101221
5148005 10 20101213
5148005 11 20101215

 The output I need is as below:








Tbl_Key Position prin_date_key init_date_key Indicator
5148005 1 20101214 NULL P
5148005 10 NULL 20101213 I

Logic:

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???

Thanks,

4 REPLIES
Supporter

Re: SQL Query - Help

In case the min date is on position 1 would you expect 1 or 2 result rows?

Enthusiast

Re: SQL Query - Help

Two rows:

Tbl_Key Position prin_date_key init_date_key Indicator
5148005 1 20101213 NULL P
5148005 1 NULL 20101213 I

Its ok if I dont have Position in my output. I can use the indicator.

Supporter

Re: SQL Query - Help

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.

Enthusiast

Re: SQL Query - Help

Many Thanks for looking into it.. that helps.