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
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
;
1 Like
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
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
,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
;
1 Like