Getting every other value in a select
This might be a bit odd, but here we go.. I have some data in a table that has the following structure: CREATE TABLE `gaugereportinglist` ( `Updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `StartTimeAndDate` datetime NOT NULL, `Time_Offset` double default NULL, `OutgoingPcntGgeDev` float default NULL, `IncomingPcntGgeDev` float default NULL, `MillSpeed` float default NULL, `PassNumber` int(2) default NULL, KEY `STADIndex` (`StartTimeAndDate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The data is logged once a second. The StartTimeAndDate will be the same for the particular workpiece that I am interested in. I pull out the data with a select statement such as select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 This can give me, say, 3 to 4 thousand rows. I am using BIRT (www.eclipse.org/birt) to do my reporting. These value are shown on a graph. However 3000 points on a small graph on a web page is a little over the top, and takes a long time to plot. Is there any way to select say, every 10th point without doing anything on the client side?
Re: Getting every other value in a select
[EMAIL PROTECTED] wrote on 03/02/2006 06:55:14 AM: This might be a bit odd, but here we go.. I have some data in a table that has the following structure: CREATE TABLE `gaugereportinglist` ( `Updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `StartTimeAndDate` datetime NOT NULL, `Time_Offset` double default NULL, `OutgoingPcntGgeDev` float default NULL, `IncomingPcntGgeDev` float default NULL, `MillSpeed` float default NULL, `PassNumber` int(2) default NULL, KEY `STADIndex` (`StartTimeAndDate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The data is logged once a second. The StartTimeAndDate will be the same for the particular workpiece that I am interested in. I pull out the data with a select statement such as select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 This can give me, say, 3 to 4 thousand rows. I am using BIRT (www.eclipse.org/birt) to do my reporting. These value are shown on a graph. However 3000 points on a small graph on a web page is a little over the top, and takes a long time to plot. Is there any way to select say, every 10th point without doing anything on the client side? You can do it if you store your current results into a temporary table with an auto_increment column in it. Then you can run a query on your temporary table looking for rows where MOD(auto_inc_column_name,10) =0 The MOD() operator returns the remainder that comes from dividing the first parameter by the second. http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html If you wanted to get every 20th term, just change the 10 to a 20. See the pattern? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Getting every other value in a select
[EMAIL PROTECTED] wrote: This might be a bit odd, but here we go.. I have some data in a table that has the following structure: [SNIP] The data is logged once a second. The StartTimeAndDate will be the same for the particular workpiece that I am interested in. I pull out the data with a select statement such as select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 This can give me, say, 3 to 4 thousand rows. I am using BIRT (www.eclipse.org/birt) to do my reporting. These value are shown on a graph. However 3000 points on a small graph on a web page is a little over the top, and takes a long time to plot. Is there any way to select say, every 10th point without doing anything on the client side? A cheap solution, with a user variable: select `OutgoingPcntGgeDev` from gaugereportinglist where `StartTimeAndDate`=2006-03-02 09:36:09 and (@count := coalesce( @count, 0) + 1 ) % 10 = 0 ; ciao gmax -- _ _ _ _ (_|| | |(_| The Data Charmer _| http://datacharmer.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]