OK, I really didn't think it through, GROUP BY or DISTINCT won't help here. Sorry for the noise. Cheers, Csaba. On Tue, 2006-01-24 at 15:11, Csaba Nagy wrote: > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly
