CODEWITHSUNDEEP
sqlsql-server-2014
AmanMJ
AmanMJ

Reputation: 117

How to get alternate row using SQL query?

I have a requirement which I need to produce the result that returns alternately 1 and 0.

SELECT * 
FROM 
    (SELECT
         id
         ,itemNo
         ,convert(int,tStationsType_id) as tStationsType_id
         ,tSpecSetpoint_descriptions_id
         ,SetpointValue
         ,rowEvenOdd 
     FROM 
         TEST S
     INNER JOIN
         (SELECT 
              itemNo AS IM, tStationsType_id as ST,
              ROW_NUMBER() OVER(PARTITION  BY itemNo ORDER BY itemNo) % 2 AS rowEvenOdd
          FROM TEST
          GROUP BY itemNo, tStationsType_id) A ON S.itemNo = A.IM
                                               AND S.tStationsType_id = A.ST) t
WHERE
    itemno = '1000911752202'
ORDER BY 
    tStationsType_id

The result I get is something like below.

I would like to produce alternate 1 and 0 in rowEvenOdd. However I notice it I can't get it alternate if I order by tStationsType_id.

So basically, what I want is when the

enter image description here

Can someone help me with this query?

Thanks.

Upvotes: 2

Views: 87

Answers (3)

cyberj0g
cyberj0g

Reputation: 3787

If you just need alternating 0 and 1 in the result set, use SEQUENCE like this:

CREATE SEQUENCE EvenOdd
   START WITH 0
   INCREMENT BY 1
   MAXVALUE 1
   MINVALUE 0
   CYCLE;
GO
SELECT SalesId, NEXT VALUE FOR EvenOdd as EvenOddColumn FROM Sales
DROP SEQUENCE EvenOdd

To learn more, go to the MSDN page on sequences here: https://msdn.microsoft.com/en-us/library/ff878091.aspx

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44316

Use DENSE_RANK instead of ROW_NUMBER:

SELECT * FROM 
(  
  SELECT
    id,
    itemNo
    convert(int,tStationsType_id) as tStationsType_id,
    tSpecSetpoint_descriptions_id,
    SetpointValue,
    (DENSE_RANK() OVER(ORDER BY CAST(A.ST as INT)) - 1)%2 AS rowEvenOdd
  FROM
  TEST S
  JOIN
  (
    SELECT
      itemNo AS IM,
      tStationsType_id as ST
    FROM 
      TEST
    GROUP BY
      itemNo,tStationsType_id
    )A
  ON 
    S.itemNo = A.IM
    and S.tStationsType_id = A.ST
) t
WHERE
  itemno = '1000911752202'
ORDER BY
  tStationsType_id

Upvotes: 0

Dgan
Dgan

Reputation: 10285

you can Use case when then

SELECT 

case
     when exists (SELECT 1 FROM  StationsTypeMaster 
      M WHERE  M.StationsType_id=A.StationsType_id)
     then 1
     else 0 end as rowEvenOdd 

,*
 FROM 
 (
 select id

  ,itemNo
  ,convert(int,tStationsType_id) as tStationsType_id
  ,tSpecSetpoint_descriptions_id
  ,SetpointValue
,rowEvenOdd from TEST S
inner join 
(select itemNo AS IM,tStationsType_id as ST,
ROW_NUMBER() OVER(PARTITION  BY itemNo ORDER BY itemNo)%2 AS rowEvenOdd
from TEST
group by itemNo,tStationsType_id

)A

on S.itemNo = A.IM
and S.tStationsType_id = A.ST) t
where itemno = '1000911752202'
order by tStationsType_id

Upvotes: 0

Related Questions