CODEWITHSUNDEEP
sql-server-2008
Gold
Gold

Reputation: 62474

how to show only even or odd rows in sql server 2008?

i have a table MEN in sql server 2008 that contain 150 rows.

how i can show only the even or only the odd rows ?

Upvotes: 22

Views: 178296

Answers (18)

Prastab Dkl
Prastab Dkl

Reputation: 141

SQL SERVER

Lets say you have student table with ROLL_NO column(int), and want to find the number of even and odd columns

--SOLUTION OPTION 1-USING CASE IN NESTED COLUMN,

SELECT Rolls, Count(Rolls) as Number
FROM (
    SELECT 
    CASE ROLL_NO % 2
    WHEN 0 THEN 'EVEN'
    ELSE 'ODD'
    END Rolls
    FROM STUDENT
    ) S --TABLE NAME TO IDENTIFY THE TABLE
GROUP BY Rolls;

--SOLUTION OPTION 2-USING NESTED QUERIES

SELECT 
(SELECT COUNT(*) FROM STUDENT WHERE ROLL_NO % 2 = 0) AS No_Of_Even_Rolls,
COUNT(*) - (SELECT COUNT(*) FROM STUDENT WHERE ROLL_NO % 2 = 0) AS No_Of_Odd_Rolls
FROM STUDENT;

Upvotes: 0

Prashanth Sunny
Prashanth Sunny

Reputation: 1

We can achieve by this Query To Find ODD Records

/*Query To Find ODD Result using CTE */
WITH EVEN_ODD_RESULT AS
 (
  select *, ROW_NUMBER() OVER (ORDER BY CountryID) AS ROWNUM
   FROM schema.Country_TBL
  )
  SELECT * FROM EVEN_ODD_RESULT
  WHERE (EVEN_ODD_RESULT.ROWNUM % 2) =1

Query To Find EVEN Records

/*Query To Find EVEN Result using CTE */

WITH EVEN_ODD_RESULT AS
(
   select *, ROW_NUMBER() OVER (ORDER BY CountryID) AS ROWNUM
   FROM schema.Country_TBL
 )
 SELECT * FROM EVEN_ODD_RESULT
 WHERE (EVEN_ODD_RESULT.ROWNUM % 2) = 0

Thank You

Upvotes: 0

Gowtham
Gowtham

Reputation: 41

For even values record :

select * from www where mod(salary,2)=0;

For odd values record:

select * from www where mod(salary,2)!=0;

Upvotes: 4

Praveen Kumar C
Praveen Kumar C

Reputation: 449

To select an odd id from a table:

select * from Table_Name where id%2=1;

To select an even id from a table:

select * from Table_Name where id%2=0;

Upvotes: 0

user12054192
user12054192

Reputation: 1

for SQL > odd:

select * from id in(select id from employee where id%2=1)

for SQL > Even:

select * from id in(select id from employee where id%2=0).....f5

Upvotes: -2

Mohd Kabir
Mohd Kabir

Reputation: 1

Oracle Database

ODD ROWS

select * from (select mod(rownum,2) as num , employees.* from employees) where num =0;

EVEN ROWS

select * from (select mod(rownum,2) as num , employees.* from employees) where num =1;

Upvotes: 0

Karthik Bs
Karthik Bs

Reputation: 1

To fetch even records

select *
from (select id,row_number() over (order by id) as r from table_name) T
where mod(r,2)=0;

To fetch odd records

select *
from (select id,row_number() over (order by id) as r from table_name) T
where mod(r,2)=1;

Upvotes: 0

Aubrey Love
Aubrey Love

Reputation: 1034

Here’s a simple and straightforward answer to your question, (I think). I am using the TSQL2012 sample database and I am returning only even or odd rows based on “employeeID” in the “HR.Employees” table.

USE TSQL2012;
GO

Return only Even numbers of the employeeID:

SELECT *
FROM HR.Employees
WHERE (empid % 2) = 0;
GO

Return only Odd numbers of the employeeID:

SELECT *
FROM HR.Employees
WHERE (empid % 2) = 1;
GO

Hopefully, that’s the answer you were looking for.

Upvotes: 0

Devendra Soni
Devendra Soni

Reputation: 31

Try following 

SELECT * FROM Worker WHERE MOD (WORKER_ID, 2) <> 0;

Upvotes: 0

Rahul
Rahul

Reputation: 51

odd number query:

SELECT *
  FROM   ( SELECT rownum rn, empno, ename
           FROM emp
         ) temp
  WHERE  MOD(temp.rn,2) = 1

even number query:

SELECT *
  FROM   ( SELECT rownum rn, empno, ename
           FROM emp
         ) temp
  WHERE  MOD(temp.rn,3) = 0

Upvotes: 5

Juan Marco
Juan Marco

Reputation: 826

FASTER: Bitwise instead of modulus.

select * from MEN where (id&1)=0;

Random question: Do you actually use uppercase table names? Usually uppercase is reserved for keywords. (By convention)

Upvotes: 5

Datta
Datta

Reputation: 849

  SELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) row_number, sr,sal FROM empsal) a WHERE (row_number%2) = 1

and 

      SELECT * FROM (SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) row_number, sr,sal FROM   empsal) a WHERE (row_number%2) = 0

Upvotes: 3

Akanksha Singh
Akanksha Singh

Reputation: 31

Following is for fetching even number:: Select * from MEN where Men_ID%2=0;

Following is for fetching odd number:: Select * from MEN where Men_ID%2!=0;

Here MEN is your table_name Men_ID is the column in MEN Table.

Upvotes: 0

Nishant Chandwani
Nishant Chandwani

Reputation: 11

select * from Tablename 
where id%2=0

Upvotes: 1

p.v.v Satyanarayana
p.v.v Satyanarayana

Reputation: 11

SELECT *
  FROM   
  ( 
     SELECT rownum rn, empno, ename
     FROM emp
  ) temp
  WHERE  MOD(temp.rn,2) = 1

Upvotes: 1

z-boss
z-boss

Reputation: 17608

Assuming your table has auto-numbered field "RowID" and you want to select only records where RowID is even or odd.

To show odd:

Select * from MEN where (RowID % 2) = 1

To show even:

Select * from MEN where (RowID % 2) = 0

Upvotes: 14

Pranay Rana
Pranay Rana

Reputation: 176916

Try this :

odd :

select * from( 
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1 DESC) AS 'RowNumber', 
FROM table1
) d where (RowNumber % 2) = 1

even :

select * from( 
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1 DESC) AS 'RowNumber', 
FROM table1
) d where (RowNumber % 2) = 0

Upvotes: 3

Matthew Whited
Matthew Whited

Reputation: 22443

Check out ROW_NUMBER()

SELECT t.First, t.Last
FROM (
    SELECT *, Row_Number() OVER(ORDER BY First, Last) AS RowNumber 
            --Row_Number() starts with 1
    FROM Table1
) t
WHERE t.RowNumber % 2 = 0 --Even
--WHERE t.RowNumber % 2 = 1 --Odd

Upvotes: 30

Related Questions