Re: [SQL] Proper case function
Michael Gould wrote: Gary, Based on what I read it wouldn't handle cases where the result should be MacDonald from macdonald. A function that translates “macdonald” to “MacDonald” would also translate “mackintosh” (the raincoat) to “MacKintosh”, unless you write it *really really* cleverly. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select every first/last record of a partition?
I think this would work: select distinct on (id) id, ts --and whatever other columns you want from mytab order by id, timestamp desc; On Mon, May 21, 2012 at 12:04 PM, Andreas wrote: > Hi, > > suppose a table that has records with some ID and a timestamp. > > id, ts > 3, 2012/01/03 > 5, 2012/01/05 > 7, 2012/01/07 > 3, 2012/02/03 > 3, 2012/01/05 > 5, 2012/03/01 > 7, 2012/04/04 > > to fetch every last row of those IDs I do: > > select id, ts > from ( select id, ts, row_number() over ( partition by id order by > ts desc ) as nr from mytab ) as x > where nr = 1 > > > Is there a another way without a subselect? > There might be more columns so the window-functions first/last won't help. > > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- 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
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. >
