Joshua Goodwin Joshua Goodwin -4 years ago 70
SQL Question

SQL to get first date and amount per account

I want to get back the date and amount of the first transaction per account in a transaction table. The table (GiftHeader) looks like this:

EntityID Date Amount
1 1/1/2027 00:00:00:00 1.00
1 2/1/2027 00:00:00:00 2.00
2 2/1/2027 00:00:00:00 4.00
2 3/1/2027 00:00:00:00 2.00


In this case, I would expect the following:

EntityID BatchDate Amount
1 1/1/2027 00:00:00:00 1.00
2 2/1/2027 00:00:00:00 4.00


Here's the SQL I'm using which isn't working.

select DISTINCT entityid, min(BatchDate) as FirstGiftDate
from GiftHeader
group by EntityId,BatchDate
order by EntityId


Any help would be appreciated.

Regards,

Joshua Goodwin

Answer Source

You can use top 1 with ties as below

Select top 1 with ties * from GiftHeader
    order by row_number() over (partition by entityid order by [BatchDate])

Other traditional approach is

Select * from (
   Select *, RowN = row_number() over (partition by entityid order by BatchDate) from GiftHeader  ) a
Where a.RowN = 1

Output:

+----------+-------------------------+--------+
| EntityId |        BatchDate        | Amount |
+----------+-------------------------+--------+
|        1 | 2027-01-01 00:00:00.000 |      1 |
|        2 | 2027-02-01 00:00:00.000 |      4 |
+----------+-------------------------+--------+
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download