4

I am recording a real time change of a given signal into my database table.

Then I draw line graph to visualize the change of the signal level.

I want to get (10n+1)th rows in the table to make a rough graph. 10 is also arbitrary. User may change it to another value.

Does someone know how to make this just using a MySQL Query

If no, I will go with PHP after selecting all the data.

Here my table structure is:

|id        |signal1   |signal2   | signal 3   |
+----------+----------+----------+------------+
|1         |0.41452   | 1.32135  | 0.31231    |
...
zkanoca
  • 9,664
  • 9
  • 50
  • 94
  • What does your table structure look like? – Aleks G Apr 25 '13 at 10:23
  • You could use `group by` on the time of the entry (manipulated to a grouping -ie format y-m-d H:i ) OR more accurately use row number http://stackoverflow.com/questions/3126972/mysql-row-number – Waygood Apr 25 '13 at 10:25
  • Do you need record number 1,10,20,30,40 and 50 like. If there is only 50 records. – JDGuide Apr 25 '13 at 10:27

2 Answers2

4

If you have an auto_incrememt id column, you can select rows that are divisible by n

SELECT * FROM tableName1 WHERE MOD(id,10)=0; 
// id divided by 10 with a remainder equal to 0 (exact)

or without sequential column id's

SELECT * FROM ( 
    SELECT 
        @row := @row +1 AS rowNum, colName1 
    FROM ( 
        SELECT @row :=0) r, tableName1
    ) ranked 
WHERE rowNum % 10 = 1
David Houde
  • 4,835
  • 1
  • 20
  • 29
  • 2
    Note that using `mod(id, 10)` will give correct results only if there are no gaps in the numbering (no rows have been deleted). – Jocelyn Apr 25 '13 at 10:29
0

If your table has auto increment id (intID), then you should try this code.There are only 26 records in my table.

select * from tablename where intId 
        in (select case  (intId%10=0) when 1 then intId else 0 end as x from tablename )

Output :-

10  sdf KK201300010 123456  Regular
20  sdf KK201300020 123456  Regular

It will displaying each record in every 10 record. Hope it will help you.

JDGuide
  • 6,239
  • 12
  • 46
  • 64
  • I am looking to implement this code that you recommended into a site but I have a few questions. What does the code mean exactly? How does it translate? "end as x" what is it doing here? do you have a recommendation as to how to best use it with php? – Rookie Recruits Oct 09 '15 at 06:10
  • To better clarify, I am trying to select every hundred rows. I am trying to create a next button that will pull every 100 rows with every click of the next button or arrow. Like a pagination but without the number of the pages. – Rookie Recruits Oct 09 '15 at 06:16