SQLTeam.com | Weblogs | Forums

Alternating classifier that marks the belonging to same group of records


The title probably sounds confusing, but it's very simple:
Given a query ordered by SALESID, and LINENUM I want to add a column that marks A for all records belonging to the same SALESID, then B for the following records belonging to the next SALESID, and then A again and so on...
I need this to give more visibility to records belonging to the same ID in a Gridview and need a marker with 2 possible values to referred to when coloring the rows in c#

SO15013	     1           A
SO15013      2           A
SO15013      3           A
SO15234      2           B
SO15234      4           B
SO15411      1           A

Off topic: Posting code has really become complicated to say it in an educated way


Something like this perhaps:

select salesid
      ,case when rn%2=1 then 'A' else 'B' end as marker
  from (select salesid
              ,row_number() over(partition by salesid) as rn
          from yourtable
       ) as a


I came up with this, which is basically the same, but yours has one wrap less... thank you!

SELECT SALESID, ord % 2 as marker from (
SELECT *, row_number() over(order by SALESID desc) ord from (


No... it doesn't work the way you propose, although the idea is correct (btw. the row_number() needs an order criteria)
I have to group first by SALESID, then get the row_number (order by SALESID) divide by 2 to get the marker (odd/even) and then join the result with the SALESLINES Table to get the lines....


You're right, it doesn't work - but I belive this will:

select b.salesid
      ,case when a.rn%2=1 then 'A' else 'B' end as marker
  from (select salesid
              ,row_number() over(order by salesid) as rn
          from yourtable
         group by salesid
       ) as a
       inner join yourtable as b
               on b.salesid=a.salesid
 order by b.salesid


Cool! Now it works!