Title: Need assist with a query that looks at 1 table but might need to subquery or join
Tags: sql;join;subquery
Question: I have table with structure as this:

```AssignID, type, PosID
1a,       e,     a
1b,      et,     a
2a,       e,     b
2b,      et,     b
```

I want my result to look like this:

```AssignID, type, PosID, NewColumn
1a,        e,     a,      1b
2a,        e,     b,      2b
```

To give a little extra detail. My data resembles the first data set. Basically PosID will duplicate twice, one with an 'e' record and one with an 'et' record. 'Et' is the child of the 'E', so i would like to eliminate the extra line and display my result as one line per 'PosID'. therefore creating a new column with the "AssignID" of the 'Et' record. hope it makes sense. 

Comment: You meant to say `type` will have a duplicate?


Here is another answer: Does this do what you want?

```select AssignID, type, PosID, replace(AssignID, 'a', 'b') as new_column
from t
where type = 'e';
```


Here is another answer: The following query would generate your expected output:

```SELECT
    MIN(AssignID) AS AssignID,
    MIN(type) AS type,
    PosID,
    MAX(AssignID) AS ExtraColumn
FROM yourTable
GROUP BY
    PosID;
```

But, it is not clear whether the above logic would work with all your actual data.  You should elaborate more on why ```e``` appears as the retained ```type```, rather than ```et```.

Demo

Comment for this answer: I don't understand your requirements then.  If you want to get here, then you should edit your question and explain the logic better.
Comment for this answer: Thank you for the reply Tim, not quiet what I am looking for. I've revised my original post, I think that explains it better now. lmk your thoughts. thanks

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2019:00:03/12/19', 'FavoriteCount': 0.0, 'PostTypeId': 1, 'Tags': '<sql><join><subquery>', 'Title': 'Need assist with a query that looks at 1 table but might need to subquery or join', 'ViewCount': 36, 'id': 55114026, 'user_AboutMe': None, 'user_AccountId': 3123809.0, 'user_DisplayName': 'Adal Raja', 'user_Reputation': 29.0, 'user_Views': 17.0, 'user_id': 2643082.0}}