Thread: SQL Newbie
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-1108: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
On 8/12/05 11:09 AM, "Lane Van Ingen" <lvaningen@esncc.com> wrote: > 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 What about (untested): SELECT a.interface_id, a.link_description, c.updated_time, c.speed FROM interface a, (select interface_id,max(speed) as speed from speed_history,interface group by interface_id)as b, speed_history c WHERE b.interface_id=a.interface_id AND c.speed=b.speed; Sean
Hope this helps SELECT* FROMspeed_history as outside etc.. WHERE(speed = ( SELECT speed FROM speed_history as inside etc.. WHERE (outside.interface =inside.interface) LIMIT 1 ORDER BY speed DESC )) Hopefully you get the idea - basically it's a corelated sub-query - very useful Nick -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Lane Van Ingen Sent: 12 August 2005 16:09 To: pgsql-sql@postgresql.org Subject: [SQL] SQL Newbie 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-1108: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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
> 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 The main idea is to join a select of max speeds grouped by interface_id to the interface table. If the query runs too slow, then you may get better performance having an index on (interface_id, speed) and using subselects. In this case you want to select information about all of interfaces and then have one of the columns be a subselect that selects one (using limit) speed from rows that have a matching interface_id ordered by interface_id desc, speed desc. This combination of limit and order by will be faster than using max.
Nick Stone wrote: > Hope this helps > > SELECT > * > FROM > speed_history as outside etc.. > WHERE > (speed = ( > SELECT > speed > FROM > speed_history as inside etc.. > WHERE > (outside.interface = inside.interface) > LIMIT 1 > ORDER BY > speed DESC > ) > ) Worth noting that ORDER BY speed DESC LIMIT 1 is fundamentally the same as MAX (speed), except that MAX (speed) doesn't take advantage of any index present. This is a fairly common idiom but a self-described SQL Newbie may not see the equivalence instantly. -Owen
On Fri, Aug 12, 2005 at 18:57:34 +0100, Nick Stone <nick@harelane.com> wrote: > Hope this helps > > SELECT > * > FROM > speed_history as outside etc.. > WHERE > (speed = ( > SELECT > speed > FROM > speed_history as inside etc.. > WHERE > (outside.interface = inside.interface) > LIMIT 1 > ORDER BY > speed DESC > ) > ) > > Hopefully you get the idea - basically it's a corelated sub-query - very > useful > Note that you want to order by interface DESC, speed DESC in the subselect and have an index on (interface, speed) for this to really be efficient. I think 8.1 might be smart enough to use an index on (interface, speed) in the above case, but currently released versions won't.