select distinct

On Sun, Aug 12, 2001 at 08:39:35PM -0400, news wrote:

It seems that I cannot get other columns
with distinct in the select statement.

I want to do like
select distinct i,a from a where c=2;

Right now I have to do
select distinct i from a where c=2
and then iterate over each i to get a

I have also tried
select a,distinct i from a where c=2

I would appreciate any suggestion

Thanks

On Sun, Aug 12, 2001 at 08:39:35PM -0400, news wrote:

It seems that I cannot get other columns
with distinct in the select statement.

I want to do like
select distinct i,a from a where c=2;

Right now I have to do
select distinct i from a where c=2
and then iterate over each i to get a

I have also tried
select a,distinct i from a where c=2

I would appreciate any suggestion

On Sun, Aug 12, 2001 at 06:14:35PM -0700, Stephan Szabo wrote:

On Sun, 12 Aug 2001 newsreader@mediaone.net wrote:

Never mind. I figure out that what
I am asking is not possible

If you're willing to use a postgres extension, you
can do (this is the 7.1 syntax, you can do it on
at least 7.0, but it's a little different):
select distinct on (i) i,a from a where c=2;
(Warning: without an order by, this statement
is not guaranteed to give the same results twice
for the same dataset)

If you know you always want a minimum/maximum a
for each i (if there is more than one a per i),
you can probably do this more standardly with
group by, something like:
select i, min(a) from a where c=2 group by i;

On Sun, Aug 12, 2001 at 08:39:35PM -0400, news wrote:

It seems that I cannot get other columns
with distinct in the select statement.

I want to do like
select distinct i,a from a where c=2;

Right now I have to do
select distinct i from a where c=2
and then iterate over each i to get a

I have also tried
select a,distinct i from a where c=2

I would appreciate any suggestion