Thomas Kellerer <spam_eater@xxxxxxx> writes: > Robert James wrote on 27.10.2013 14:04: >> I have a table (x,y,z) - I'd like to take the rows with unique x >> values - but, when more than one row have the same x value, I want the >> one with the minimal z value. >> >> How can I do that? I can imagine doing it with window functions, but >> also that regular SQL should be able to do it too. > Window functions *are* "regular" SQL ;) Indeed. The only other easy way I know of involves SELECT DISTINCT ON (see the "weather reports" example in the SELECT reference page); but that is most definitely not standard SQL, it is a Postgres-ism. > A solution without window functions could be something like: > select t1.x, t1.y, t1.z > from table t1 > join (select t2.x, min(t2.y) as min_y > from the_table t2 > group by t2.x > ) mt on mt.x = t1.x and mt.min_y = t1.y; Note that this doesn't work unless x and y form a primary key, else you get multiple join rows (or no join rows, if one is NULL). In any case, it's unlikely to be fast. I think I've seen some even more esoteric solutions that use only SQL-92-era features, but lack of caffeine prevents me from recalling them. In any case, there's a good reason why we invented SELECT DISTINCT ON: this is just not easy to do in minimal SQL. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
