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

Question

hi

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 ?

thank's in advance

Answer 1

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

Answer 2

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

Answer 3

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