Twice Inner Join on same table with Aggregate function

Question

being a novice sql user:

I have a simple table storing some records over night daily. table:

Table: T1

+----+-----+----+-----------+------------+
| Id |  A  | AB |   Value   |    Date    |
+----+-----+----+-----------+------------+
|  1 | abc | I  | -48936.08 | 2013-06-24 |
|  2 | def | A  | 431266.19 | 2013-06-24 |
|  3 | xyz | I  | -13523.90 | 2013-06-24 |
|  4 | abc | A  | 13523.90  | 2013-06-23 |
|  5 | xyz | I  | -13523.90 | 2013-06-23 |
|  6 | def | A  | 13523.90  | 2013-06-22 |
|  7 | def | I  | -13523.90 | 2013-06-22 |
+----+-----+----+-----------+------------+

I would like to get all values of columns A,AB, Value for the latest Date on Column A filtered on AB = I

basically the result should look like:

+----+-----+----+-----------+------------+
| Id |  A  | AB |   Value   |    Date    |
+----+-----+----+-----------+------------+
|  1 | abc | I  | -48936.08 | 2013-06-24 |
|  3 | xyz | I  | -13523.90 | 2013-06-24 |
|  7 | def | I  | -13523.90 | 2013-06-22 |
+----+-----+----+-----------+------------+

I have tried to use inner join twice on the same table but failed to come up with correct result.

any help would be appreciated.

thanks :)

t-clausen.dk · Accepted Answer · 2013-07-30 16:03:06Z

3

This will work with sqlserver 2005+

;WITH a as
(
SELECT id, A,AB, Value, Date
, row_number() over (partition by A order by Date desc) rn
FROM t1
WHERE AB = 'I'
)
SELECT id, A,AB, Value, Date
FROM a WHERE rn = 1

answered Jul 30, 2013 at 16:03

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gvee · Accepted Answer · 2013-07-30 16:06:11Z

3

; WITH x AS (
  SELECT id
       , a
       , ab
       , "value"
       , "date"
       , Row_Number() OVER (PARTITION BY a ORDER BY "date" DESC) As row_num
  FROM   your_table
  WHERE  ab = 'I'
)
SELECT *
FROM   x
WHERE  row_num = 1

answered Jul 30, 2013 at 16:06

gvee

17.2k42 silver badges50 bronze badges

1 Comment

gvee Over a year ago

Oh dear, too slow, was beaten with the same answer! What's the SO netiquette here? Should I delete my post?

Collectives™ on Stack Overflow

Twice Inner Join on same table with Aggregate function

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related