On 7/26/07, Bill Moseley <moseley at hank.org> wrote: > I need a little sanity check. > > I have a view "foo" that joins a number of tables. There's a column > I consider the primary key for the view called "id". > > Some of the joins are many-to-many, and therefore a select on the view > might return more than on row of a given "id". But, I always only > want one row back. > > So I'm doing this sub-query: > > SELECT DISTINCT ON (id) * > FROM ( > SELECT * > FROM foo > WHERE some_col = ? > ) AS sub_query; > > Which allow me to use a WHERE clause on all rows before the distinct. Distinct is bad in most cases (this is one of them). Not sure what exactly is being attempted here, but if you just need a distinct set of IDs, try group by. If not, see the following: http://www.onlamp.com/pub/a/onlamp/2004/09/30/from_clauses.html > Now, I also need a count of those rows: > > SELECT count(*) > FROM ( > SELECT DISTINCT ON (id) * > FROM ( > SELECT * > FROM foo > WHERE some_col = ? > ) AS sub_sub_query > ) AS sub_query; > > Which works. But, two nested queries makes me think I'm doing this > the hard way. Is there a more natural way to do this? count(*) is also bad (at least in Oracle, and I assume in general). You should not need to do this if all you are doing is getting a total rowcount of the result set. There should be built-in mechanisms to do this, either in your programming language or in the SQL dialect for your particular database. -- Matt Warden Cincinnati, OH, USA http://mattwarden.com This email proudly and graciously contributes to entropy.