LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (https://www.linuxquestions.org/questions/programming-9/)
-   -   help with mysql query: return nth rows in query (https://www.linuxquestions.org/questions/programming-9/help-with-mysql-query-return-nth-rows-in-query-469491/)

hawarden 07-31-2006 04:35 PM
help with mysql query: return nth rows in query
 
Hi,

I am trying to write a query in sql using a MySQL database that returns every nth row. The table contains the unit timestamp and some data taken at that time. I have a query that read something like
Code:
SELECT * FROM controller WHERE timestamp > $starttime LIMIT 100
This returns the first 100 records. How do I modify this query to give me every 5th record of that 100, so that I get 20 records, rather than 100?

Thanks!
Miranda

spirit receiver 07-31-2006 06:17 PM
The following seems to work:
Code:
SET @id := 0;
SELECT * FROM
  ( SELECT *, @id:=(@id + 1) AS id FROM controller
    WHERE timestamp > $starttime
    ORDER BY timestamp
    LIMIT 100 ) AS intermediate
WHERE NOT id % 5;
You'll need a recent version of MySQL for this because it uses nested queries, but you can also use a temporary table. It creates a new table with an additional column 'id' that enumerates the rows of the original table. From this intermediate table, you select all rows where id is divisible by 5.

hawarden 07-31-2006 06:36 PM
Great! Thanks a bunch. Not sure why it would matter, since the docs claim that all three syntaxes (MOD(N,M), N % M, N MOD M) work, but I had to make the following change:
Code:
SET @id := 0;
SELECT *
FROM (
  SELECT *, @id := (@id + 1) AS id
  FROM controller
  WHERE timestamp > $starttime
  ORDER BY timestamp
  LIMIT 100
) AS intermediate
WHERE NOT (id MOD 5);
in order to get rows back. "NOT id % 5" gave me a null set. But thanks a lot! That really helps.

Miranda


All times are GMT -5. The time now is 03:44 AM.