Thread: deciding on one of multiple results returned
If a query returns, say the following results:<br /><br />id value<br />0 a<br />0 b<br />0 c<br />1 a<br />1 b<br /><br /><br /><br />How do I just choose a preferred element say value 'a' over any other elementsreturned, that is the value returned is from a subquery to a larger query?<br /><br />Thanks.<br />
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.
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.
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.
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.
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;
Thanks. I was testing different things and I came up with something similar to that. I appreciate you taking time to answer.<br/><br /><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Fri, Dec 21, 2012 at 11:22 AM, Scott Marlowe<span dir="ltr"><<a href="mailto:scott.marlowe@gmail.com" target="_blank">scott.marlowe@gmail.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px#ccc solid;padding-left:1ex"><div class="im">On Fri, Dec 21, 2012 at 10:28 AM, Wes James <<a href="mailto:comptekki@gmail.com">comptekki@gmail.com</a>>wrote:<br /> > David and Seth Thanks. That helped.<br />><br /> ><br /> > When I have<br /> ><br /> > select distinct on (revf3) f1, f2, f3, revers(f3) as revf3from table order<br /> > by revf3<br /> ><br /> > Is there a way to return just f1, f2, f3 in my results andforget revf3 (so<br /> > it doesn't show in results)?<br /><br /></div>Sure just wrap it in a subselect:<br /><br />select a.f1, a.f2, a.f3 from (select distinct on (revf3) f1, f2, f3,<br /> revers(f3) as revf3 from table order by revf3)as a;<br /></blockquote></div><br /></div>