ARWinner Dec 31, 2004   3:18 AM GMT

The ORDER BY clause is for sorting--after the result set is selected. That is why the function is not allowed, it is sorta meaningless to count again. Try this: SELECT COUNT(*) as quantity, DAYS(TMSTP_COL) FROM MYTAB A GROUP BY DAYS(TMSTP_COL) HAVING COUNT(*) > 1 ORDER BY quantity DESC or this: SELECT COUNT(*), DAYS(TMSTP_COL) FROM MYTAB A GROUP BY DAYS(TMSTP_COL) HAVING COUNT(*) > 1 ORDER BY 1 DESC HTH Andy

0 pointsBadges:

BigKat Jan 2, 2005   10:45 AM GMT

Give the columns names and group by the column not the function. select .... AS NAME .... group by NAME....

9,430 pointsBadges:

Stanton Jan 3, 2005   10:48 AM GMT

Here is what you have to do, I've tested it to make sure it works. select count(*) THECOUNT, X.M_DAYS FROM (SELECT DAYS(TMSTP_COL) M_DAYS FROM MYTAB) X GROUP BY X.M_DAYS HAVING COUNT(*) > 1 ORDER BY THECOUNT DESC Stanton

0 pointsBadges:

TomLiotta Oct 19, 2009   8:58 PM GMT

I've always given my derived columns names because I've never worked out the rules. The SQL Reference (V5R3) has this to say: If GROUP BY or HAVING is used:

• Each column-name in the select list must identify a grouping expression or be specified within a column function:
  • – If the grouping expression is a column name, the select list may apply additional operators to the column name. For example, if the grouping expression is a column C1, the select list may contain C1+1.
  • – If the grouping expression is not a column name, the select list may not apply additional operators to the expression. For example, if the grouping expression is C1+1, the select list may contain C1+1, but not (C1+1)/8.

That last bit -- "If the grouping expression is not a column name" -- sure makes it sound like a column name is not required. And the example -- "the grouping expression is C1+1" -- sure seems to show that a column name isn't required. But nothing is said about a SCALAR function either way. I've never figured out the exact rules, so I just do like others have suggested -- [DAYS(TMSTP_COL) as NewCol] -- in the SELECT list and reference [NewCol] elsewhere. Tom

125,585 pointsBadges:

TomLiotta Oct 21, 2009   1:33 AM GMT

And as a test, this works in V5R3:

SELECT
   count(*),days(SYSLCHG)
 FROM mytesttbl
   GROUP BY days(SYSLCHG)
   HAVING count(*)<>0

As does this:

SELECT
   count(*),days(SYSLCHG)
 FROM mytesttbl
   GROUP BY days(SYSLCHG)
   HAVING count(*)<>0
   ORDER BY  1  desc

The first discussion point by ARWinner is on the mark. Of course, that assumes that TMSTP_COL is a timestamp. Tom

125,585 pointsBadges: