>I was expecting to see one row for > >"melvin comms directors 30.04.13" and also for "DCFTG210513". > >QUERY >select '['|| TRIM(SUBSTRING(COMMENTS FROM 1 FOR 100)) || ']', count(*) >from target >group by 1 >having count(*)>5 >order by 1 > >RESULTS >[null] 2289 >[melvin comms directors 30.04.13] 17 >[melvin comms directors 30.04.13] 52 >[melvin comms directors 30.04.13] 18 >[melvin comms directors 30.04.13] 8 >[melvin comms directors 30.04.13] 6 >[DCFTG210513] 14 >[Rackable/SGI] 10 >[microsoft james 15.03] 15 >[microsoft james 15.03] 8 >[NWSEI060613] 6 >[DCFTG210513] 19 >[DCFTG210513] 19 >[lifesiences] 7 >[DCFTG210513] 37 >[DCFTG210513] 17
Needless to say, your result seems to be completely wrong, both regarding result and ordering, and I certainly cannot explain that. What happens if you try: with tmp(MyComment) as (select '['|| TRIM(SUBSTRING(COMMENTS FROM 1 FOR 100)) || ']' from target) select MyComment, count(*) from tmp group by 1 having count(*) > 5 order by 1 Have you checked your database for errors? Your current result looks like an error to me (though I'm not even qualified to guess whether the error is in your database or Firebird itself). Are you using a recent Firebird version and do you get your erroneous result using isql or some other tool? Set
