From: Bruno Wolff III <bruno@wolff.to>
To: Lane Van Ingen <lvaningen@esncc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Newbie
Date: Fri, 12 Aug 2005 13:04:55 -0500
Message-ID: <20050812180455.GA32286@wolff.to> (raw)
In-Reply-To: <EKEMKEFLOMKDDLIALABIKEEECBAA.lvaningen@esncc.com>
References: <EKEMKEFLOMKDDLIALABIKEEECBAA.lvaningen@esncc.com>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, lvaningen@esncc.com
Subject: Re: SQL Newbie
In-Reply-To: <20050812180455.GA32286@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox