CODEWITHSUNDEEP
sqldb2ibm-midrange
Carlo
Carlo

Reputation: 25969

Simple query in DB2 for AS400

This is a very easy query (I think) but I stil can't do it.

I have some data like this:

A          B        C
1        1100       5  
1        1100       5
1         500       1
2        1200       4
2        1200       4
2         600       1
3        1300       3
3        1300       3
3         700       1

And I want to return the top B per A with the SUM of C, or something like this:

A     B    C
1   1100   10
2   1200   8
3   1300   6

Also, I'm using DB2 for AS400, so I can't use the TOP keyword.

EDIT @ OMG Ponies:

I tried somethine like

SELECT
   t.A
   ,MAX(t.B)
   ,SUM(t.C)
FROM t
GROUP BY
   t.A

But it returns the total sum of C, not only the ones selected:

A    B    C
1   1100  11
2   1200  9
3   1300  7

Thanks!

Upvotes: 2

Views: 3872

Answers (2)

Leslie
Leslie

Reputation: 3644

On DB2 you can use the FETCH FIRST # ROWS ONLY to mimic the TOP predicate but it goes at the END of the SQL statement

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332771

Looks like you want the max B value for each A value, so

  SELECT t.a,
         MAX(t.b)
    FROM TABLE t
GROUP BY t.a

If you want entire record associated with the A and MAX(b) values, use:

SELECT t.a,
       t.b,
       t.c
  FROM TABLE t
  JOIN (SELECT x.a,
               MAX(x.b) AS max_b
          FROM TABLE x
      GROUP BY x.a) y ON y.a = t.a
                     AND y.max_b = t.b

Upvotes: 4

Related Questions