List:General Discussion« Previous MessageNext Message »
From:Gordon Date:January 27 2005 5:26pm
Subject:RE: How to select every second record
View as plain text  
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.blanchard@stripped] 
Sent: Thursday, January 27, 2005 8:50 AM
To: Alessandro Sappia; mysql@stripped
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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1



Thread
How to select every second recordMartin Rytz27 Jan
  • Re: How to select every second recordMartijn Tonies27 Jan
RE: How to select every second recordJay Blanchard27 Jan
RE: How to select every second recordJay Blanchard27 Jan
  • RE: How to select every second recordGordon27 Jan
RE: How to select every second recordJay Blanchard27 Jan