Thread: distinct

I have attempted to run
select distinct col1 from tablename;
and it runs fine.

When I run it in a real world environment:
select distinct col1, col2, col3, col4 from tablename order by col1 desc,
col2;
It returns all the values from col1 which appears to be the same thing as
not using distinct at all.

Am I missing something? I'd like only distinct values on col1 and then I'll
sort, include, or exclude the other columns.

Andrew

On Tue, 3 Oct 2000, Otwell, Andrew (ISSAtlanta) wrote:

> I have attempted to run
> select distinct col1 from tablename;
> and it runs fine.
>
> When I run it in a real world environment:
> select distinct col1, col2, col3, col4 from tablename order by col1 desc,
> col2;
> It returns all the values from col1 which appears to be the same thing as
> not using distinct at all.
>
> Am I missing something? I'd like only distinct values on col1 and then I'll
> sort, include, or exclude the other columns.

The DISTINCT keyword means that the row returned (all values
considered together) is
DISTINCT. Not just that the first variable is DISTINCT. So a row having
values of 'a','b' and another row with 'a','c' are distinctly different in
that the second variable is not the same. I don't know of a way to say
that you want "col1" to be distinct. In the above case, which row should
be returned? I.e. if there are two different values for col2, which single
one is returned? random? lowest value?


I hope this makes sense, but I fear that I didn't do a good job of
explaining.

John

On Tue, 3 Oct 2000, Otwell, Andrew (ISSAtlanta) wrote:

> I have attempted to run
> select distinct col1 from tablename;
> and it runs fine.
>
> When I run it in a real world environment:
> select distinct col1, col2, col3, col4 from tablename order by col1 desc,
> col2;
> It returns all the values from col1 which appears to be the same thing as
> not using distinct at all.
>
> Am I missing something? I'd like only distinct values on col1 and then I'll
> sort, include, or exclude the other columns.

That syntax above says get distinct values for all of col1, col2, col3,
col4, so (1,2,3,4) and (1,2,3,5) are not distinct and would both be
output.

There really isn't a spec way to do it, however,
 select distinct on (col1) col1, col2, col3, col4 from tablename;
would get you one row for each distinct col1, but it's undefined what
row that gets you.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> There really isn't a spec way to do it, however,
>  select distinct on (col1) col1, col2, col3, col4 from tablename;
> would get you one row for each distinct col1, but it's undefined what
> row that gets you.

You can get a well-defined result with DISTINCT ON if you sort by
*all* the columns, because we specify that DISTINCT ON yields the
first row of each group that are the same in the DISTINCT-ON'd columns.
So for example,

select distinct on (col1, col2) col1, col2, col3, col4 from tablename
order by col1, col2, col3, col4 desc;

would give you the minimum col3, and then the maximum col4 for that col3
value, within the rows having any particular col1 & col2 values.

This is a big kluge, no question, but there are cases where it gives
you just what you want and there's no good way to get the same result
in standard SQL.  For example, the above query is NOT equivalent to

select col1, col2, min(col3), max(col4) from tablename
group by col1, col2;

That'll give you the same col1, col2, and col3 results --- but the col4
maxima will be across the whole of each col1/col2 group, and will not
necessarily come from the same rows the col3 values come from.

There was a nice example of an application where this was Just The Right
Thing in the discussions that led up to DISTINCT ON being cleaned up,
rather than axed, for 7.0.  It was either pgsql-general or pgsql-sql,
I forget, but check the archives for discussion of DISTINCT ON between
6.5 and 7.0 release dates.

            regards, tom lane