NYCPHP Meetup

[Andrew Yochum]

On Wed, Feb 01, 2006 at 04:33:58PM -0500, Carlos A Hoyos wrote:
> If some ids have been removed, you can use a subquery to get the row
> number.
> 
> For example, if you have a table "your_table" with primary key
> "your_table_id", this query will list they key and the row # for this
> entry.
> 
> select (select SUM(1) from your_table where your_table_id
> <=tableAlias.your_table_id) as RowNumber,  your_table_id from your_table_id
> tableAlias;
> 
> add a "having mod(RowNumber,2) =0" to list every other row.

Clever.  Here's a similar and possibly more efficient solution:

    SET @I = 0;
    SELECT ID FROM SomeTable having mod(@I:=@I+1,2) = 0;

Be sure to reset @I after each use or you'll get funky results.

HTH,
Andrew

-- 
Andrew Yochum
Plexpod
andrew at plexpod.com
718-360-0879