Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

Mysql query to get the first record for a set

Mysql query to get the first record for a set.

For each Acct# I want to get the old (first) deposit

Here is my data
Acct#  Date           Deposit
Smith  01-Jan-10   100.00
Smith  01-Feb-10   200.00
Jones  01-Mar-10   50.00
Brown 01-Jan-10   10.00
Brown 01-Apr-10   20.00


I want the result
Acct#  Date           Deposit
Smith  01-Jan-10   100.00
Jones  01-Mar-10   50.00
Brown 01-Jan-10   10.00
0
pmsguy
Asked:
pmsguy
  • 3
  • 2
  • 2
  • +2
9 Solutions
 
dozenmattaCommented:
Try something like:

Select AcctNum, Max(TrxDate), Deposit
from Accounts
group by TrxDate

0
 
dozenmattaCommented:
Sorry, i meant

Try something like:

Select AcctNum, Max(TrxDate), Deposit
from Accounts
group by AcctNum
0
 
dirknibleckCommented:
Use MIN(TrxDate) to get the oldest (first) record, otherwise dozenmatta has it correct.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
pmsguyAuthor Commented:
Let me rephrase my question.

For each Acct# I want to get the old (first) deposit and sum all the deposits in 1 sql statement.

I assume a sub query is needed to get the old (first) deposit.
0
 
dirknibleckCommented:
Ok. If you just want the amount of the first deposit (or deposits on the first date)...:

SELECT AcctNum, first_deposit, SUM(Deposit)

FROM Accounts, (SELECT AcctNum, MIN(TrxDate) as first_deposit FROM Accounts GROUP BY AcctNum) as agg

WHERE Accounts.AcctNum = agg.AcctNum AND Accounts.TrxDate = agg.first_deposit
0
 
dirknibleckCommented:
* Add *

GROUP BY AcctNum, first_deposit
0
 
danrosenthalCommented:
I think this would work:

SELECT d.AcctNo, d.Date, d.Deposit
FROM Data d
WHERE NOT EXISTS (
      SELECT 1 FROM Data d2 WHERE d2.AcctNo = d.AcctNo AND d2.Date < d.Date
)

0
 
danrosenthalCommented:
To get the old (first deposit) date and the sum of all deposits you would simply do this:

SELECT AcctNo, MIN(Date) AS FirstDeposit, SUM(Deposit) AS TotalDeposits
FROM Data
GROUP BY AcctNo
0
 
cyberkiwiCommented:
In case you have 2 deposits on the same first day, this will still work

select `Acct#`, Min(`Date`) `Date`, Min(FirstDeposit) FirstDeposit, Max(SumDeposit) SumDeposit
from
(
select `Acct#`, `Date`, Deposit,
  @d:=case when @a=`Acct#` then null else Deposit end as FirstDeposit,
  @s:=case when @a=`Acct#` then @s+ifnull(Deposit,0) else Deposit end as SumDeposit,
  @a:=`Acct#` as Discard
from (select @a:=null,@d:=null,@s:=null) r, mydata
order by `Acct#`, `Date` ASC
) n
group by `Acct#`
order by `Acct#`
0
 
cyberkiwiCommented:
In my previous query, the columns are to be interpreted:

`Acct#` - account
Min(`Date`) `Date`  - date of first deposit
Min(FirstDeposit) FirstDeposit   - amount of first deposit
Max(SumDeposit) SumDeposit   - total of all deposits
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now