[thelist] SQL descrete and count

Bill Moseley moseley at hank.org
Thu Jul 26 00:11:01 CDT 2007


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.

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?





-- 
Bill Moseley
moseley at hank.org




More information about the thelist mailing list