OraFAQ Forum: Server Administration » group by and having with concatinated field

Home » RDBMS Server » Server Administration » group by and having with concatinated field

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

group by and having with concatinated field [message #371235]

Thu, 21 September 2000 16:53

Don Keyes
Messages: 3
Registered: September 2000

Junior Member

We have a number of table with fiscal year and fiscal month fields. In Sybase we are able to find the latest records with a statement like:

select a,b,c
from x
group by a,b,c
having (fisc_yr || fisc_mth) = max(fisc_yr || fisc_mth)

When I try to run that in Oracle it gives me the error:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

having (fisc_yr||fisc_mth) = max(fisc_yr || fisc_mth)
*
ERROR at line 12:
ORA-00979: not a GROUP BY expression

I tried adding the fisc_yr and fisc_mth fields to the select but it didn't help.

Is there any way to using the "having" clause on a combination of fields?

Thanks

Report message to a moderator

Re: group by and having with concatinated field [message #371238 is a reply to message #371235]

Fri, 22 September 2000 02:41

Letchoumy
Messages: 5
Registered: September 2000

Junior Member

I think that you can try this query

select a,b,c
from x
where (fisc_yr || fisc_mth)=
(select max(fisc_yr || fisc_mth) from x) ;

Report message to a moderator

Re: group by and having with concatinated field - found it! [message #371250 is a reply to message #371238]

Fri, 22 September 2000 12:35

Don Keyes
Messages: 3
Registered: September 2000

Junior Member

Hi,

Thanks for the suggestion. I had tried something similar but it only gave me the records that matched the maximum year and month
combination for the whole table. I need the maximum year-month combination for each value of field a.

I modified your suggestion and came up with this code that worked.

field a is the unique key field

select a,b,c
from tablex x1
where (x1.fisc_yr || x1.fisc_mth)=
(select max(x2.fisc_yr || x2.fisc_mth) from tablex x2
where x1.a = x2.a
group by x2.a)
group by x1.a,
x1.b,
x1.c

Joining the second table back to the first table on the key field a did the trick.

Thanks for pointing me in the right direction.

Report message to a moderator

Re: group by and having with concatinated field [message #371255 is a reply to message #371235]

Sat, 23 September 2000 01:32

Mahesh Pednekar
Messages: 28
Registered: August 2000

Junior Member

Try This :-
select a,b,c
from x
group by a,b,c,fisc_yr || fisc_mth
having (fisc_yr || fisc_mth) = max(fisc_yr || fisc_mth)

Hope this will solve your problem.
Enjoy with oracle
Mahesh.

Report message to a moderator

Previous Topic:	Performance issues with concatenation operator
Next Topic:	what function should I use ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 07:13:47 CDT 2024