Merge two rows in sql and create new columns

Question

My Input data (data in table is):

I have this requirement where I need to find first and last entry as well as first entered and last entered/modified. When I have a query like (Not exact query - this query is BIG MESS, I am simplifying for sake of PoC):

Select field1, field2, min(EnteredDate), max(EnteredDate) from table where field = <fieldvalue> group by field1 AND EnteredBy

Don't ask me why we are grouping by "EnteredBy", this is the mess I am dealing and done by former team.

Right now the results are something like

Actually the output of the query should be like below:

Can someone please provide guidance on how I can achieve this? Thank you!

Expected output in text format:

Field1  Field2  First entered by    First entered date  Last entered By Last Entered Date
1   hello   User1   3/9/2022    User2   3/11/2022
2   somenew User2   3/10/2022   User1   3/11/2022

Answer 1

To get the first / last rows' values with some specified ordering, use the window functions first_value / last_value

The window frame clause must be specified explicitly for LAST_VALUE to work since the default frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which always yields the current row for LAST_VALUE.

DISTINCT drops all duplicate rows in the following query and returns only 1 row for each combination of field1, field2

SELECT DISTINCT
  field1
, field2
, FIRST_VALUE(entered_by) OVER w first_entered_by
, FIRST_VALUE(entered_date) OVER w first_entered_date
, LAST_VALUE(entered_by) OVER w last_entered_by
, LAST_VALUE(entered_date) OVER w last_entered_date
FROM mytable
WINDOW w AS (
  PARTITION by field1, field2
  ORDER BY entered_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY field1

Here's a DB Fiddle illustrating how to do it with sample data & set up code