[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.
Re: [SQL] deciding on one of multiple results returned
From: [email protected] [mailto:[email protected]] On Behalf Of Wes James Sent: Friday, December 21, 2012 11:32 AM To: [email protected] 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.
Re: [SQL] deciding on one of multiple results returned
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 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. >
Re: [SQL] deciding on one of multiple results returned
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 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 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. >> > >
Re: [SQL] deciding on one of multiple results returned
On Fri, Dec 21, 2012 at 10:28 AM, Wes James 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] deciding on one of multiple results returned
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 wrote: > On Fri, Dec 21, 2012 at 10:28 AM, Wes James 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; >
