SQLTeam.com | Weblogs | Forums

Alternating classifier that marks the belonging to same group of records


#1

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#

SALESID     LINENUM     MARKER
-------------------------------
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


#2

Something like this perhaps:

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

#3

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 (
SELECT SALESID
FROM  SALESLINES
GROUP BY SALESID)a)b

#4

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....


#5

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

select b.salesid
      ,b.linenum
      ,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
;

#6

Cool! Now it works!