11/1/15

show only even or odd rows in sql

Hi Readers,

If we need to find records from table where for some column to be even or odd.

Example:-
We have table State with columns

  1. ID
  2. NAME
  3. CITY
  4. STATUS
Now you want to find :


To select records with odd ID:


Select * from State where ID % 2 = 1

(This query will give you all rows from State table with odd column ID)

To select records with even ID:


Select * from State where ID % 2 = 0

(This query will give you all rows from State table with even column ID)

For Mysql

Additionally if column to apply even and odd is not directly available then we will use Aliasing

Fetch odd rows


SELECT col1, col2
FROM (
   SELECT col1, col2, @rowNumber:=@rowNumber+ 1 rn
   FROM YourTable
      JOIN (SELECT @rowNumber:= 0) r
) t 
WHERE rn % 2 = 1

Fetch even rows


SELECT col1, col2
FROM (
   SELECT col1, col2, @rowNumber:=@rowNumber+ 1 rn
   FROM YourTable
      JOIN (SELECT @rowNumber:= 0) r
) t 
WHERE rn % 2 = 0