Alban, what you want is to put the "sort_order <> 1" in the WHERE clause, not in the HAVING clause. Then it will do what you want. Cheers, Csaba. On Tue, 2006-01-24 at 13:51, Alban Hertroys wrote: > Michael Glaesemann wrote: > > > > On Jan 24, 2006, at 20:00 , Alban Hertroys wrote: > > > >> Though this does give the right results, I would have liked to be > >> able to use NOT HAVING. Or is there a way using HAVING that would > >> give the same results? I'm quite sure HAVING sort_order <> 1 doesn't > >> mean the same thing. > > > > Why are you so sure? It seems to me that NOT HAVING sort_order = 1 and > > HAVING sort_order <> 1 would mean semantically the same thing. Can you > > show that HAVING sort_order <> 1 gives incorrect results? > > There's a difference in meaning. By NOT HAVING sort_order = 1 I mean > there is no record in the grouped records that has sort_order = 1. In > contrast HAVING sort_order <> 1 means there is a record in the group > with a sort_order other than 1, even if there's also a sort_order = 1 in > the grouped records. > > To illustrate, say we have sort_orders 1,2,3,4,5: > - NOT HAVING sort_order = 1 would result false > - HAVING sort_order <> 1 would result true > > If we'd have 2,3,4,5: > - NOT HAVING sort_order = 1 would result true > - HAVING sort_order <> 1 would result true > > If we'd have 1 only: > - NOT HAVING sort_order = 1 would result false > - HAVING sort_order <> 1 would result false > > But it seems HAVING can't be applied to columns not in the group by or > an aggregate. No idea why that might be...
