select every Nth element in a column

[Anon]

Hi,

How can I select e.g. every seventh element in a column using a MySQL statement?

Thanks.

Thomas.

[Anon]

Sure you can.

Just use a for loop and increment by, say "7" each time. Use the incremented number as part of the mysql_result function

for ($i = 0; $i < mysql_num_rows($result), $i = $i + 7)
{
$variable = mysql_result($result, $i, "column_name");
}

Now, this is the answer to the question that I think you were asking. If this does do what you want, maybe I misunderstood the question.

[Anon]

I was looking for a SQL statement that does that.

Thomas.

[Anon]

What is the database you are using ? Has the table got a numerical primary key ?

[spider_man]

If is't effectively ordered by a PK and starting on zero, then:

select * from table where id mod 6 = 0;

Although you should throw away the first row.

[Anon]

I am using MySQL and I have not indexed my data. Can we get every seventh element without indexing? Can we get it with indexing? how?

[bitdaemon]

Index key or primary key is very important .. yah can't do what you want or do sophisticated SQL statement w/o it, so you should put and index key to your table.

you can set an index key via ALTER table command if your are using MySQL server. Check http://www.mysql.com/doc/A/L/ALTER_TABLE.html for further details. And then you can proceed to what Pablo had just posted:

"select * from table where id mod 6 = 0";

Goodluck. #;^,]

[[deleted]]

SELECT * FROM table LIMIT 7,1;

manuals are cool.

[bitdaemon]

hrmm, i think you understand it wrong vincent? .. he wants ever NTH of a column not ALL of the data from start to the NTH column. =)

[[deleted]]

sigh

for ($t=0;$number_of_rows;$t+=7)
{
$query="SELECT * FROM table LIMIT $t,1";
};

[pblamire]

But now there will be many queries to the database, rather than one, surely this is what we want to avoid?

[[deleted]]

Usually.

But there is not real other way to do it, because adding a numeric index solves nothing.
Yes it sounds good, selecting where id mod 7=0, but what if I delete record number 1-6?
I still select ID's 0 and 7, but are no records left inbetween, making record id 7 the 2nd record.
Et presto, wrong answers.

You could add a numeric index, then query to get all the record ids, then use PHP to process the ids to get only every 7th, then use a SELECT WHERE id IN (recordsids) to ge the data.

[Anon]

<?
mysql_connect("colorado.hq.q-go.com", "bugtrack", "bugtrack");
mysql_select_db("authdev");
mysql_query("select @i:=-1");
$sql="select distinct IF(!mod(@i:=@i+1,7),<field_you_need>,NULL) from <table>";
$result=mysql_query($sql);

while (list($key,$val)=mysql_fetch_row($result))
{
if ($val!="") echo "$val<br>";
}

?>

voila...

there will be 1 single NULL value in your result set. Nothing i can do about that. So you'll have to filter that one out later.

[Anon]

Seems like we can't find a SQL statement that does this.

[Anon]

"select @i:=-1; select distinct IF(!mod(@i:=@i+1,7),<field_you_need>,NULL) from <table>"

This is the sql statements you need to run... (inside the "")

[[deleted]]

You can't put two statements into one mysql_query(),
but it also works if you run the two queries directory after eachother.

Nice work Jeroen!

[Anon]

I tried to run this from the mysql prompt. It didn't seem to work.

Weten jullie waarom?

[Anon]

It works but it displays NULL for the first row. How can we get rid of that NULL?

Thanks.

Thomas

[[deleted]]

Simplest way: put a tiny statement in PHP that tells it to ignore the result if it's NULL. :-)

[Anon]

Actually, that's the only way 🙂
You can't get rid of the NULL using sql thequery