[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