pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddeciding on one of multiple results returned
6+ messages / 4 participants
[nested] [flat]
* deciding on one of multiple results returned
@ 2012-12-21 16:31 Wes James <comptekki@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Wes James @ 2012-12-21 16:31 UTC (permalink / raw)
To: pgsql-sql
If a query returns, say the following results:
id value
0 a
0 b
0 c
1 a
1 b
How do I just choose a preferred element say value 'a' over any other
elements returned, that is the value returned is from a subquery to a
larger query?
Thanks.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deciding on one of multiple results returned
@ 2012-12-21 16:46 David Johnston <polobo@yahoo.com>
parent: Wes James <comptekki@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David Johnston @ 2012-12-21 16:46 UTC (permalink / raw)
To: 'Wes James' <comptekki@gmail.com>; pgsql-sql
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Wes James
Sent: Friday, December 21, 2012 11:32 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] deciding on one of multiple results returned
If a query returns, say the following results:
id value
0 a
0 b
0 c
1 a
1 b
How do I just choose a preferred element say value 'a' over any other
elements returned, that is the value returned is from a subquery to a larger
query?
Thanks.
ORDER BY
(with a LIMIT depending on circumstances)
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deciding on one of multiple results returned
@ 2012-12-21 16:57 Seth Gordon <sethg@ropine.com>
parent: Wes James <comptekki@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Seth Gordon @ 2012-12-21 16:57 UTC (permalink / raw)
To: Wes James <comptekki@gmail.com>; +Cc: pgsql-sql
If you only want one value per id, then your query should be “SELECT
DISTINCT ON (id) ...”
If you care about which particular value is returned for each ID, then you
have to sort the results: e.g., if you want the minimum value per id, your
query should be “SELECT DISTINCT ON (id) ... ORDER BY value”. The database
will sort the query results before running them through the DISTINCT filter.
On Fri, Dec 21, 2012 at 11:31 AM, Wes James <comptekki@gmail.com> wrote:
> If a query returns, say the following results:
>
> id value
> 0 a
> 0 b
> 0 c
> 1 a
> 1 b
>
>
>
> How do I just choose a preferred element say value 'a' over any other
> elements returned, that is the value returned is from a subquery to a
> larger query?
>
> Thanks.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deciding on one of multiple results returned
@ 2012-12-21 17:28 Wes James <comptekki@gmail.com>
parent: Seth Gordon <sethg@ropine.com>
0 siblings, 1 reply; 6+ messages in thread
From: Wes James @ 2012-12-21 17:28 UTC (permalink / raw)
To: pgsql-sql; David Johnston <polobo@yahoo.com>; +Cc: Seth Gordon <sethg@ropine.com>
David and Seth Thanks. That helped.
When I have
select distinct on (revf3) f1, f2, f3, revers(f3) as revf3 from table
order by revf3
Is there a way to return just f1, f2, f3 in my results and forget revf3 (so
it doesn't show in results)?
Thanks.
On Fri, Dec 21, 2012 at 9:57 AM, Seth Gordon <sethg@ropine.com> wrote:
> If you only want one value per id, then your query should be “SELECT
> DISTINCT ON (id) ...”
>
> If you care about which particular value is returned for each ID, then you
> have to sort the results: e.g., if you want the minimum value per id, your
> query should be “SELECT DISTINCT ON (id) ... ORDER BY value”. The database
> will sort the query results before running them through the DISTINCT filter.
>
>
> On Fri, Dec 21, 2012 at 11:31 AM, Wes James <comptekki@gmail.com> wrote:
>
>> If a query returns, say the following results:
>>
>> id value
>> 0 a
>> 0 b
>> 0 c
>> 1 a
>> 1 b
>>
>>
>>
>> How do I just choose a preferred element say value 'a' over any other
>> elements returned, that is the value returned is from a subquery to a
>> larger query?
>>
>> Thanks.
>>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deciding on one of multiple results returned
@ 2012-12-21 18:22 Scott Marlowe <scott.marlowe@gmail.com>
parent: Wes James <comptekki@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2012-12-21 18:22 UTC (permalink / raw)
To: Wes James <comptekki@gmail.com>; +Cc: pgsql-sql; David Johnston <polobo@yahoo.com>; Seth Gordon <sethg@ropine.com>
On Fri, Dec 21, 2012 at 10:28 AM, Wes James <comptekki@gmail.com> wrote:
> David and Seth Thanks. That helped.
>
>
> When I have
>
> select distinct on (revf3) f1, f2, f3, revers(f3) as revf3 from table order
> by revf3
>
> Is there a way to return just f1, f2, f3 in my results and forget revf3 (so
> it doesn't show in results)?
Sure just wrap it in a subselect:
select a.f1, a.f2, a.f3 from (select distinct on (revf3) f1, f2, f3,
revers(f3) as revf3 from table order by revf3) as a;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: deciding on one of multiple results returned
@ 2012-12-21 18:50 Wes James <comptekki@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Wes James @ 2012-12-21 18:50 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql; David Johnston <polobo@yahoo.com>; Seth Gordon <sethg@ropine.com>
Thanks. I was testing different things and I came up with something
similar to that. I appreciate you taking time to answer.
On Fri, Dec 21, 2012 at 11:22 AM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Fri, Dec 21, 2012 at 10:28 AM, Wes James <comptekki@gmail.com> wrote:
> > David and Seth Thanks. That helped.
> >
> >
> > When I have
> >
> > select distinct on (revf3) f1, f2, f3, revers(f3) as revf3 from table
> order
> > by revf3
> >
> > Is there a way to return just f1, f2, f3 in my results and forget revf3
> (so
> > it doesn't show in results)?
>
> Sure just wrap it in a subselect:
>
> select a.f1, a.f2, a.f3 from (select distinct on (revf3) f1, f2, f3,
> revers(f3) as revf3 from table order by revf3) as a;
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-12-21 18:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-12-21 16:31 deciding on one of multiple results returned Wes James <comptekki@gmail.com>
2012-12-21 16:46 ` David Johnston <polobo@yahoo.com>
2012-12-21 16:57 ` Seth Gordon <sethg@ropine.com>
2012-12-21 17:28 ` Wes James <comptekki@gmail.com>
2012-12-21 18:22 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-12-21 18:50 ` Wes James <comptekki@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox