|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/25/2009 8:37:01 PM
Posts: 2,
Visits: 13
|
|
Can some please help me as I am using Teradata Sql Assistant 7.2 and I want to execute the following query:
SELECT Column1 ,Column2 ,SUM(Column3)
FROM my_table
WHERE Column4 = '2000'
GROUP BY 1,2
QUALIFY MAX(Column2) OVER ( PARTITION BY Column4) = Column2;
when I run the code i will got "selected non-aggregate values must be part of the associated group" error.
but , if I run the code as below, it will be ok:
SELECT A.* FROM
(
SELECT Column1 ,Column2 ,SUM(Column3)
FROM my_table
WHERE Column4 = '2000'
GROUP BY 1,2
QUALIFY MAX(Column2) OVER ( PARTITION BY Column4) = Column2;
)A
so, I want to know the reason? What should be the problem?
FYI:"having" have the same problem as "qualify"
Many thanks!!!
Regards,
Gary
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/30/2009 6:28:19 AM
Posts: 34,
Visits: 117
|
|
Hi Gary,
Your 2nd query will also not run. It'll throw the same error - as you got in the First Query.
How you're getting the result!
The Column4 has to be there in Select if you partition by Column4.
Or otherwist you've to take the partition by Col1 / Col2
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/25/2009 8:37:01 PM
Posts: 2,
Visits: 13
|
|
hi Animesh,
Thx for your writing!
But u should trial what I said before u make the improper conclusion...
I know what u said, pls trial the strange problem in teradata...
BTW, the "having" has the same problem in teradata, FYI.
Thank you all the same!Thank you!!!
Thanks & Regards,
Gary
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hi Gary,
You will be getting following error with your second query:
1. Syntax error 3706: All expression in the derived table must have an explicit name.
After adding the alias name to sum() column you will be getting another error:
2. Error 3504: Selected non-aggregate values must be part of associated group
As Animesh pointed out if you modify your query as below you be will be able to run it:
SELECT A.* FROM
(
SELECT Column1 ,Column2 ,SUM(Column3) as C, Column4
FROM my_table
WHERE Column4 = '2000'
GROUP BY 1,2,4
QUALIFY MAX(Column2) OVER ( PARTITION BY Column4) = Column2
)A
Regards,
Balamurugan
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
I have not tried your query, but I agree with Balamurugan that I do not think your second query will work either.
I think the following will get what you are after:
SELECT Column1 ,Column2 ,SUM(Column3)
FROM my_table
WHERE Column4 = '2000'
QUALIFY RANK() Over (Partition By Column4 ORDER BY Column2 Desc) = 1
GROUP BY 1,2
;
The Qualify Rank will get you all the rows where Column2 is at its maximum. You do not really need the Partition By because your Where clause limits the included rows.
HTH
|
|
|
|