From steve@candata.com Wed Dec 13 19:02:46 2000 Received: from grunt.candata.com (gateway.candata.com [204.225.91.106]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDJ2kj16163 for ; Wed, 13 Dec 2000 14:02:46 -0500 (EST) (envelope-from steve@candata.com) Received: from candata.com (steve@grunt.candata.com [192.168.1.15]) by grunt.candata.com (8.9.3/8.9.3) with ESMTP id OAA18067 for ; Wed, 13 Dec 2000 14:03:20 -0500 Message-ID: <3A37C7F2.A39482E4@candata.com> Date: Wed, 13 Dec 2000 14:03:14 -0500 From: Steve Meynell Organization: CanData X-Mailer: Mozilla 4.74 [en] (X11; U; Linux 2.2.5-15 i586) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Selecting Most Recent Row Content-Type: multipart/alternative; boundary="------------3FB08B666743E4FE417C32E7" X-Archive-Number: 200012/108 X-Sequence-Number: 442 --------------3FB08B666743E4FE417C32E7 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Ok here is the problem. Table: Widgets Fields: Unique_Key, DateStamp, Batch_Number, Instructions. Basic Select Statement: select Unique_Key from Widgets where Batch_Number>='inputedvalue' Problem: Some Batch_Numbers might be duplicated over a period of time. I want to select the most recent of these Batch Numbers. -- Steve Meynell Candata Systems --------------3FB08B666743E4FE417C32E7 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Ok here is the problem.
Table: Widgets
Fields:  Unique_Key, DateStamp, Batch_Number, Instructions.

Basic Select Statement:
select Unique_Key from Widgets where Batch_Number>='inputedvalue'

Problem:
Some Batch_Numbers might be duplicated over a period of time.  I want to select the most recent of these Batch Numbers.
 

-- 
Steve Meynell
Candata Systems
  --------------3FB08B666743E4FE417C32E7-- From jburton@scw.org Wed Dec 13 21:38:21 2000 Received: from olympus.scw.org (209-9-156-98.sdsl.cais.net [209.9.156.98]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDLcLX45263 for ; Wed, 13 Dec 2000 16:38:21 -0500 (EST) (envelope-from jburton@scw.org) Received: from hades (eos.scw.org [209.9.156.112]) by olympus.scw.org (8.10.1/8.10.1) with ESMTP id eBDLce305742; Wed, 13 Dec 2000 16:38:40 -0500 From: "Joel Burton" Organization: Support Center of Washington To: Steve Meynell Date: Wed, 13 Dec 2000 16:38:23 -0500 MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Subject: Re: Selecting Most Recent Row CC: pgsql-sql@postgresql.org Message-ID: <3A37A5FF.26823.13E1C98@localhost> In-reply-to: <3A37C7F2.A39482E4@candata.com> X-mailer: Pegasus Mail for Win32 (v3.12c) X-Archive-Number: 200012/112 X-Sequence-Number: 446 > Ok here is the problem. > Table: Widgets > Fields: Unique_Key, DateStamp, Batch_Number, Instructions. > > Basic Select Statement: > select Unique_Key from Widgets where Batch_Number>='inputedvalue' > > Problem: > Some Batch_Numbers might be duplicated over a period of time. I want > to select the most recent of these Batch Numbers. Will DateStamp being the date of insertion? If so, is it that you want the record for the most recent (largest) datestamp for each batch_number? something like SELECT DISTINCT ON (batch_number) unique_key, datestamp, batch_number, instructions FROM widgets ORDER BY batch_number, datestamp desc; (sort by batch then by date (last first) and show the first (aka 'distinct') row, considering only the batch_number for distinctness) HTH. -- Joel Burton, Director of Information Systems -*- jburton@scw.org Support Center of Washington (www.scw.org) From steve@candata.com Wed Dec 13 22:08:46 2000 Received: from grunt.candata.com (gateway.candata.com [204.225.91.106]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDM8kX48707 for ; Wed, 13 Dec 2000 17:08:46 -0500 (EST) (envelope-from steve@candata.com) Received: from candata.com (steve@grunt.candata.com [192.168.1.15]) by grunt.candata.com (8.9.3/8.9.3) with ESMTP id RAA18548; Wed, 13 Dec 2000 17:09:15 -0500 Message-ID: <3A37F38A.D52497C0@candata.com> Date: Wed, 13 Dec 2000 17:09:14 -0500 From: Steve Meynell Organization: CanData X-Mailer: Mozilla 4.74 [en] (X11; U; Linux 2.2.5-15 i586) X-Accept-Language: en MIME-Version: 1.0 To: Joel Burton CC: pgsql-sql@postgresql.org Subject: Re: Selecting Most Recent Row References: <3A37A5FF.26823.13E1C98@localhost> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/114 X-Sequence-Number: 448 Joel, Thank you very much. I gave that a try and it worked perfectly. It definately was the distinct keyword I was missing. Thanks Again, Steve Joel Burton wrote: > > Will DateStamp being the date of insertion? If so, is it that you want > the record for the most recent (largest) datestamp for each > batch_number? > > something like > > SELECT DISTINCT ON (batch_number) unique_key, datestamp, > batch_number, instructions FROM widgets ORDER BY batch_number, > datestamp desc; > > (sort by batch then by date (last first) and show the first (aka > 'distinct') row, considering only the batch_number for distinctness)