Select MIN day within each month

Question

I am trying to pull min day within each month/Year (it is not always the first) - and balance and account that is attached to that day

This is what I currently have:

SELECT [ACCT10]
  ,[MyDATE]
  ,[BALANCE]
   rownum=row_number() OVER(
   PARTITION BY DATEADD(month,DATEDIFF(month,0,MyDate),0),[ACCT10]
   order by MyDate asc
   ) FROM
   [Accounts]
   )
    SELECT [ACCT10]
    ,[MyDATE]
    ,[BALANCE]
   FROM ranked where rownum = 1
   FROM [Accounts]

DATA:

Current

ACCT10	MyDate	BALANCE
X546785e	1/1/2023	57200
X546785e	1/2/2023	57500
X546785e	1/5/2023	59050
X546785e	2/3/2023	57800
X546785e	2/4/2023	60500
X546785e	2/5/2023	61200
X5s5485e	1/2/2023	16500
X5s5485e	1/5/2023	16520
X5s5485e	1/6/2023	19800
X5s5485e	2/1/2023	15000

Desired

ACCT10	MyDate	BALANCE
X546785e	1/1/2023	57200
X546785e	2/3/2023	57800
X5s5485e	1/2/2023	16500
X5s5485e	2/1/2023	15000

Unfortunately, I am unable to use the EXTRACT command

Answer 1

The basics of that query seem OK, it just needs some tweaking to correct the syntax:

WITH ranked
AS (
    SELECT [ACCT10]
        , [MyDATE]
        , [BALANCE]
        , row_number() OVER (
            PARTITION BY DATEADD(month, DATEDIFF(month, 0, MyDate), 0)
            , [ACCT10] ORDER BY MyDate ASC
            ) AS rownum
    FROM [Accounts]
    )
SELECT [ACCT10]
    , [MyDATE]
    , [BALANCE]
FROM ranked
WHERE rownum = 1