LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 07-31-2006, 04:35 PM   #1
hawarden
Newbie
 
Registered: Feb 2005
Location: Hawaii
Distribution: centos/fedora/redhat/solaris
Posts: 28

Rep: Reputation: 15
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
 
Old 07-31-2006, 06:17 PM   #2
spirit receiver
Member
 
Registered: May 2006
Location: Frankfurt, Germany
Distribution: SUSE 10.2
Posts: 424

Rep: Reputation: 33
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.

Last edited by spirit receiver; 07-31-2006 at 06:20 PM.
 
Old 07-31-2006, 06:36 PM   #3
hawarden
Newbie
 
Registered: Feb 2005
Location: Hawaii
Distribution: centos/fedora/redhat/solaris
Posts: 28

Original Poster
Rep: Reputation: 15
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

Last edited by hawarden; 07-31-2006 at 06:37 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
MYySQL Query return more rows than exist in table paddyjoy Programming 4 02-27-2006 11:39 PM
MySQL Query Help Gerardoj Programming 1 06-10-2004 09:54 PM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 12:27 PM
mysql query ? shaahul Linux - Software 1 09-06-2003 03:35 AM
mysql query ????? shaahul Linux - Newbie 1 09-06-2003 03:28 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 04:34 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration