CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
deutschZuid
deutschZuid

Reputation: 1058

A more graceful way to aggregate columns

Here is a simplified transactions table.

Each row is a transaction that has a unique transac id (identity field if you will), an accountpointer (foreign key to the accounts table which is not displayed here), a transac date and a dollar amount.

Sample data below:

trans_id acc_ptr trans_date amount
1        12      2011-08-24 2.0
2        12      2011-08-25 3.0
3        14      2011-07-28 -3.0
4        16      2011-06-14 -1.0
5        12      2011-05-15 0.5
6        16      2011-07-30 -2

What I want is very simple. Display the most recent transac grouped by acc_ptr including the amount for that date.

What I have works perfectly, but what I would like to know is, is there a more elegant way (as far as programming goes) or more efficient way of dealing with this problem, specifically my treatment of the subquery for amount? Would like to see how you would approach it.

My approach:

select acc_ptr
, max(trans_date) as [most rec transac date]
, (select amount from transactions t2 
where t2.trans_date = max(t1.trans_date) 
and t2.acc_ptr = t1.acc_ptr) as amount
from transactions t1
group by acc_ptr

Upvotes: 3

Views: 180

Answers (3)

Gopal Sanodiya
Gopal Sanodiya

Reputation: 204

This can be done by simple query

select tans_id, amount from tablename 
group by acc_ptr
having date =max(date)

result is :

trans_id    amount
3           -3
6           -2
2            3

Upvotes: -2

8kb
8kb

Reputation: 11406

To note, your subquery could fail in the case of two transactions on the same day: 

DECLARE @transactions TABLE (
  trans_id INT, 
  acc_ptr INT, 
  trans_date datetime, 
  amount money
) 

INSERT @transactions VALUES 
(1,12,'2011-08-24',2.0),
(2,12,'2011-08-25',3.0),
(3,14,'2011-07-28', -3.0),
(4,16,'2011-06-14', -1.0),
(5,12,'2011-05-15', 0.5),
(6,16,'2011-07-30', -2),
(7,16,'2011-07-30', -1) -- New transaction

select acc_ptr,
  max(trans_date) as [most rec transac date],
 (select amount 
  from @transactions t2 
  where t2.trans_date = max(t1.trans_date) 
  and t2.acc_ptr = t1.acc_ptr) as amount
  from @transactions t1
  group by acc_ptr

Result: 

Msg 512, Level 16, State 1, Line 17
Subquery returned more than 1 value. 
This is not permitted when the subquery follows =, !=, <, <= , >, >= 
or when the subquery is used as an expression.

OMG Ponies solves the issue of the tie breaker by using ROW_NUMBER.

Another option to consider: 

SELECT
  acc_ptr,
  amount,
  trans_date
FROM transactions t1
WHERE trans_id = 
  (SELECT
     MAX(trans_id)
   FROM transactions t2
   WHERE acc_ptr = t1.acc_ptr
   AND trans_date = 
     (SELECT
        MAX(trans_date) 
      FROM transactions
      WHERE acc_ptr = t2.acc_ptr)
   )

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332581

The first alternative that comes to mind is to use analytics (IE: ROW_NUMBER), but that's SQL Server 2005+ functionality.

WITH example AS (
  SELECT t.acc_ptr,
         t.trans_date AS [most rec transac date],
         t.amount,
         ROW_NUMBER() OVER (PARTITION BY t.acc_ptr 
                                ORDER BY t.trans_date DESC) AS rnk
    FROM transactions t)
SELECT e.acc_tpr,
       e.trans_date,
       e.amount
  FROM example e
 WHERE e.rnk = 1

There's no performance value in this example to using a CTE (WITH syntax) - this is equivalent:

SELECT e.acc_tpr,
       e.trans_date,
       e.amount
  FROM (SELECT t.acc_ptr,
               t.trans_date AS [most rec transac date],
               t.amount,
               ROW_NUMBER() OVER (PARTITION BY t.acc_ptr 
                                      ORDER BY t.trans_date DESC) AS rnk
          FROM transactions t) e
 WHERE e.rnk = 1

Upvotes: 3

Related Questions