X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7500852E34 for ; Fri, 12 Aug 2005 12:06:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34091-03 for ; Fri, 12 Aug 2005 15:06:14 +0000 (GMT) Received: from TWMAIL.ESNCC.COM (static-66-173-159-28.t1.cavtel.net [66.173.159.28]) by svr1.postgresql.org (Postfix) with ESMTP id 7143552DFF for ; Fri, 12 Aug 2005 12:06:12 -0300 (ADT) Received: from spawar2i8uvlb9 ([150.125.117.63]) by TWMAIL.ESNCC.COM with Microsoft SMTPSVC(5.0.2195.6713); Fri, 12 Aug 2005 11:06:10 -0400 From: "Lane Van Ingen" To: Subject: SQL Newbie Date: Fri, 12 Aug 2005 11:09:00 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181 Importance: Normal X-OriginalArrivalTime: 12 Aug 2005 15:06:10.0653 (UTC) FILETIME=[5FB000D0:01C59F4F] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=[FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200508/68 X-Sequence-Number: 22296 It seems to me that I should be able to do this, but after 5 hrs of trying, I can't figure this one out. I could do this in two queries, but seems like I should be able to do this in one. What I am trying to do: Find the highest speed at which each interface of a router has run over time. I have three tables, two of which (interface, speed_history) are being used in this query (primary / foreign key fields noted as PK / FK): router -> 1:M -> interface -> 1:M -> speed_history ------------------- --------------------------- -------------------------- - router_no (int2) PK interface_id (int4) PK interface_id (int4) PK name (varchar) router_no (int2) FK updated_time (timestamp) PK link_description (varchar) speed (int4) Data in speed history looks like this: interface_id updated_time speed 1 2005-08-11 08:10:23 450112 1 2005-08-11 10:53:34 501120 <--- 1 2005-08-11 10:58:11 450112 2 2005-08-11 08:10:23 450112 <--- 2 2005-08-11 11:00:44 350234 3 2005-08-11 08:10:23 450112 <--- The rows of speed_history I want back are marked above with ' <--- '. Query results should look like: interface.interface_id interface.link_description speed_history.updated_time speed_history.speed