CODEWITHSUNDEEP
mysqlsql
Rafay
Rafay

Reputation: 601

how to get even odd ids in different columns on single query?

For example I have a table like given below .. I want to have separate columns on the basis of even/odd ids

-----------------------------------------------------
| ID  |  Names
-----------------------------------------------------
| 1   |  Name1
-----------------------------------------------------
| 2   |  Name2
-----------------------------------------------------
| 3   |  Name3
-----------------------------------------------------
| 4   |  Name4
-----------------------------------------------------

I want to design a query that could give me 

-------------------
| Even  |  Odd    |
-------------------
| Name2 |  Name1  |
-------------------
| Name4 |  Name3  |
-------------------

Upvotes: 0

Views: 2873

Answers (6)

Neha Patil
Neha Patil

Reputation: 1

The above result set can be achieved by the following code -

SELECT Even, Odd   
FROM (SELECT *, ROW_NUMBER()OVER(ORDER BY Even) AS ROW   
    FROM (SELECT CASE WHEN ID % 2 = 0 THEN Names ELSE NULL END AS 'Even'  
    FROM TableName)TAB1  
    WHERE Even IS NOT NULL)T1  
FULL OUTER JOIN   
(SELECT *, ROW_NUMBER()OVER(ORDER BY Odd) AS ROW  FROM  
    (SELECT CASE WHEN ID % 2 = 1 THEN Names ELSE NULL END AS 'Odd'  
    FROM TableName) TAB2  
    WHERE Odd IS NOT NULL) T2  
ON T1.ROW=T2.ROW

Upvotes: 0

Ishwar Lal
Ishwar Lal

Reputation: 654

If you ID is sequential then

SELECT tb1.ODD, tb2.EVEN
FROM
(
    SELECT Id, name ODD
    FROM YourTable
    where (`Id` % 2) = 1
 ) AS tb1 

 JOIN 

 (SELECT Id, name EVEN
  FROM  YourTable
  WHERE (`Id` % 2) = 0
 ) AS tb2 

ON (tb1.Id + 1 = tb2.Id)

WHERE tb1.ODD IS NOT NULL
ORDER BY tb1.Id

Upvotes: 0

Deep
Deep

Reputation: 3202

If your Id column contain sequential number without any gap between number then :

SELECT t1.name as ODD,
       t2.name as EVEN
FROM   YourTable t1
       left outer JOIN YourTable t2
         ON t1.Id + 1 = t2.Id
where t1.Id%2 = 0

Note : if there are gaps between number then some of the ODD will be shown as NULL or it may skip that name if it has gap of more than 3.

Upvotes: 2

Jaugar Chang
Jaugar Chang

Reputation: 3196

select 
  max(case id%2 when 1 then name end) as odd,
  max(case id%2 when 0 then name end) as even
from your_table
group by floor((id+1)/2)

SQL Fiddle Demo

Upvotes: 8

Darshan Nayak
Darshan Nayak

Reputation: 1

Find the parity (property of even or odd) using modulo operator %.

...where id%2 equals 0;

This will give you even id in result.

The ones which are not equal to 0 are the odd id.

Upvotes: 0

Dmitriy.Net
Dmitriy.Net

Reputation: 1500

If you want to get odd or even, use next queries: Select for odd records:

SELECT * FROM table WHERE ID % 2 = 1

Select, for even

SELECT * FROM table WHERE ID % 2 = 0

And if you want to decorate as two columns, try next solution:

SELECT
    odd.name as Odd,
    (SELECT name FROM table WHERE ID = odd.ID + 1 ) as Even
FROM
    table as odd
WHERE
    odd.ID % 2 = 1

Upvotes: 1

Related Questions