tags:

views:

34

answers:

0

I have the following sample table structure:

id | facilityId | accountId |   date   | timestamp | amount
-------------------------------------------------------------
1       ABC          100      20100401   272300000     55

2       ABC          300      20100401   272300000     50

3       ABC          100      20100402   272400000     60

4       ABC          300      20100402   272400000     10

5       ABC          100      20100402   272500000     30

6       ABC          100      20100402   272600000     35

7       ABC          300      20100402   272700000     30

My sudo-query is: I'm trying to get the sum(amount) as totalAmount for each distinct accountId, where date >= 20100401, using the most recent timestamp of each accountId. I'm looking for some help with the query/sub-select requirements.

Example result:

accountId | totalAmount
------------------------
   100          90

   300          80

I specifically want to check and use the latest timestamp, and not rely on the id.

Thanks.