X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id BAA9B9FA61D for ; Mon, 11 Sep 2006 21:15:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 30204-09 for ; Tue, 12 Sep 2006 00:15:25 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id F2A139FA613 for ; Mon, 11 Sep 2006 21:15:24 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k8C0FKJP008534; Mon, 11 Sep 2006 20:15:20 -0400 (EDT) To: gabor cc: pgsql-sql@postgresql.org Subject: Re: text+number, find largest entry In-reply-to: <4505F28E.6060606@nekomancer.net> References: <4505F28E.6060606@nekomancer.net> Comments: In-reply-to gabor message dated "Tue, 12 Sep 2006 01:34:38 +0200" Date: Mon, 11 Sep 2006 20:15:20 -0400 Message-ID: <8533.1158020120@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200609/74 X-Sequence-Number: 26285 gabor writes: > i have a table, where there is a varchar(500) column, > which contains data that is strangely formatted: > it starts with letters, and ends with a number. > for example: > xyz001 > xyz002 > xyz044 > xyz1243 > abc01 > abc993 > abc2342 > now, for a given text-prefix (for example "xyz"), i need to > find the record with the largest "numeric component". I'd try using a couple of regexp_replace() calls to pull out the text prefix and number separately. Then you could group by the one and order by the other in your favorite variant of the DISTINCT ON pattern. (See weather-report example in the SELECT reference page if you have no idea what I'm talking about.) > this lookup does not have to be especially fast. Good ;-) ... otherwise changing your schema would definitely be indicated. regards, tom lane