[thelist] SQL descrete and count

Matt Warden mwarden at gmail.com
Thu Jul 26 00:43:14 CDT 2007


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.



More information about the thelist mailing list