RE: How to select every second record - Gordon - com.mysql.lists.mysql

6 messages in com.mysql.lists.mysqlRE: How to select every second record

From	Sent On	Attachments
Martin Rytz	27 Jan 2005 06:19
Martijn Tonies	27 Jan 2005 06:30
Jay Blanchard	27 Jan 2005 06:30
Jay Blanchard	27 Jan 2005 06:50
Gordon	27 Jan 2005 09:26
Jay Blanchard	27 Jan 2005 09:37

Subject:	RE: How to select every second record
From:	Gordon (gor...@mailprint.com)
Date:	01/27/2005 09:26:12 AM
List:	com.mysql.lists.mysql

Try this. The second set ... select gives you what you want. However, the group by may interfere with the rest of your logic. You also don't really need the mod(@a,2) in the result set, just in the having. mysql> set @a:=0; Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=@a+1,mod(@a,2),ordr_ID, poft_Sub_Month from er_poft limit 10; +----------+-----------+---------+----------------+ | @a:=@a+1 | mod(@a,2) | ordr_ID | poft_Sub_Month | +----------+-----------+---------+----------------+ | 1 | 1 | 4245 | 01 | | 2 | 0 | 4323 | 01 | | 3 | 1 | 4328 | 01 | | 4 | 0 | 4329 | 01 | | 5 | 1 | 4331 | 01 | | 6 | 0 | 4332 | 01 | | 7 | 1 | 4333 | 01 | | 8 | 0 | 4335 | 01 | | 9 | 1 | 4343 | 01 | | 10 | 0 | 4344 | 01 | +----------+-----------+---------+----------------+ 10 rows in set (0.00 sec)

mysql> set @a:=0; Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=@a+1,mod(@a,2),ordr_ID, poft_Sub_Month from er_poft group by 3,4 having mod(@a,2) = 0 limit 5; +----------+-----------+---------+----------------+ | @a:=@a+1 | mod(@a,2) | ordr_ID | poft_Sub_Month | +----------+-----------+---------+----------------+ | 2 | 0 | 4323 | 01 | | 4 | 0 | 4329 | 01 | | 6 | 0 | 4332 | 01 | | 8 | 0 | 4335 | 01 | | 10 | 0 | 4344 | 01 | +----------+-----------+---------+----------------+ 5 rows in set (0.00 sec)

-----Original Message----- From: Jay Blanchard [mailto:jay....@niicommunications.com] Sent: Thursday, January 27, 2005 8:50 AM To: Alessandro Sappia; mys...@lists.mysql.com Subject: RE: How to select every second record

[snip] Jay Blanchard wrote:

[snip] Is it possible to select only every second record from a record set?

I should select the record-number 1, 3, 5, 7, 9, ... or record-number 2, 4, 6, 8, ...

Can this be done with LIMIT? [/snip]

Not LIMIT, but you can use MOD, especially with an auto-increment field (id in this case is the auto-increment field)

select * from table where mod(id, 2) <> '0' returns odd rows select * from table where mod(id, 2) <> '1' returns even rows

You have to do it with LIMIT beacuse id may not help you... so select * from table where <condition> [group by <field>] [order by <field> [desc]] LIMIT 2,1

this select just second resultrow from any kind of resultset made using every thing you like in where/order by/groub by and not being limited by

using IDs (auto_increment) [/snip]

The problem is that this only returns ONE record, the OP wanted every other record