SQL "group by" statement

I am hoping someone out there can help.....

I am currently successfully running the following query with the following results:

> select max(trd_buy_qty), prod_type_cd from trd_vol_dly group by prod_type_cd;

MAX(TRD_BUY_QTY) PROD_TYPE_CD
------------------------- -------------------
5669 01
128989 02
3263 03
5654 04
1241 05
363 06
4461 07
917 09
89 10
330 14
626 15
883 16
1857 17

13 rows selected.

I now, however, want to retrieve that exact set of data only with the date that the max(trd_buy_qty) occurred (which is stored in the table as AS_OF_DA) as well.

If I just try to add AS_OF_DA as shown here I get the following error:

> select max(trd_buy_qty), prod_type_cd, as_of_da from trd_vol_dly group by prod_type_cd;

ERROR at line 1:
ORA-00979: as_of_da not a GROUP BY expression

And if I add AS_OF_DA to the group by clause as shown below I get the max value for EACH day and basically get every row in the table (and I only want the max value for ALL days):

> select max(trd_buy_qty), prod_type_cd, as_of_da from trd_vol_dly group by prod_type_cd, as_of_da;

If anyone out there knows a solution to the problem it would be greatly appreciated.

Thanks in advance.....

Don't post Oracle queries in the Mysql forumr.

swampBoogie,

Impressive. Okay, that gives the high across all prod_type_cd's for each day, but I'm looking for the high for each prod_type_cd for the entire tenure and the day that each high occurred. There should be 13 rows. Any ideas there?

My fault for not cleary relaying the issue.

Thanks in advance.

Also, this is optional, but is it possible to do without the IN statement and the embedded SQL? After creating this query I am going to attempt to port this into WebFocus and it's SQL bytecode will not allow for the IN statement.

Thanks again.

Anyone....anyone....HEEEEELP.

I played with this a bit and came up with essentially the same thing as swampboogie.

That query would assume that the combination of prod_type_cd and as_of_da is unique, Is that the case? We either aren't understanding you or there is something about the data that we don't know about.

I came up with 3 different methods that you may find useful to work around your db issue. I have no idea what webfocus is so I'm no help there.