You're right, but only if there's no GROUP BY. As soon as you use a GROUP BY _and_ the mentioned WHERE clause, the result will be what the OP wanted... or you could use SELECT DISTINCT for what he wanted. On Tue, 2006-01-24 at 15:02, Will Glynn wrote: > Alban Hertroys wrote: > > > Richard Huxton wrote: > > > >> Alban Hertroys wrote: > >> You're mixing up WHERE and HAVING. The WHERE clause applies to the > >> individual rows before GROUP BY. The HAVING applies to the output of > >> the GROUP BY stage. > > > > > > Ah, of course, now it makes sense. Combined with Csaba's reply my > > original problem has vaporized. Thank you guys :) > > > Csaba's response is incorrect: > > >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. > > > If you do that, the query reads "give me unique values for some_column > from some_table, ignoring individual records that have sort_order=1". > > To illustrate, say we have sort_orders 2,3,4,5: > - NOT HAVING sort_order = 1 would result true > - HAVING sort_order <> 1 would result true > - WHERE sort_order <> 1 would result true for all records > > If we'd have 1 only: > - NOT HAVING sort_order = 1 would result false > - HAVING sort_order <> 1 would result false > - WHERE sort_order <> 1 would result false > > If we'd have 1,2,3,4,5: > - NOT HAVING sort_order = 1 would result false > - HAVING sort_order <> 1 would result true > - WHERE sort_order <> 1 would result true for records 2,3,4,5, returning > some_column anyway, which is not what you want > > This can be done with an aggregate, a sub-select, or a JOIN -- there's > no way to do this using only a single-table WHERE. > > --Will Glynn > Freedom Healthcare > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
