Postgres Pro
Downloads
Home   >   mailing lists

Thread: select distinct

select distinct

From
newsreader@mediaone.net
Date:
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

Re: select distinct

From
newsreader@mediaone.net
Date:
Never mind.  I figure out that what
I am asking is not possible

Sorry for the noise

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
>

Re: Re: select distinct

From
newsreader@mediaone.net
Date:
That's beautiful!

Thanks a lot.



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

Re: Re: select distinct

From
Stephan Szabo
Date:
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