ROW_NUMBER()
Hi,
I have a column in a table that has X's and A's in it. How do I produce a row_number column that starts a new number everytime it hits an X so the results will be as below - Thanks
1 X
1 A
1 A
2 X
2 A
2 A
3 X
4 X
5 X
I have a column in a table that has X's and A's in it. How do I produce a row_number column that starts a new number everytime it hits an X so the results will be as below - Thanks
1 X
1 A
1 A
2 X
2 A
2 A
3 X
4 X
5 X
ASKER
Thanks not sure I understand, can you provide the SQL syntax please
nutnut,
Does your table have some other column in it that we can rely on for sorting, such as an identity or a time stamp? Without that, the sort order (and thus the relative position of each X) is entirely arbitrary and unreliable.
Patrick
Does your table have some other column in it that we can rely on for sorting, such as an identity or a time stamp? Without that, the sort order (and thus the relative position of each X) is entirely arbitrary and unreliable.
Patrick
ASKER
Sorry yes good point it does it has a datetime column that I haven't shown for ease of explaining the issue....pehaps it may have been a good idea for me to include!
As mattewspatrick suggests, suppose you have an ID field, you can do this:
select Id, XField,
,(select sum(XField) from MyTable
where ID <= M.ID and XField = 'X')
'Running Total'
from MyTable M
Thanks
select Id, XField,
,(select sum(XField) from MyTable
where ID <= M.ID and XField = 'X')
'Running Total'
from MyTable M
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks all
Why not use the COUNT function as per
http://msdn.microsoft.com/en-us/library/ms175997.aspx
because the number on the right column is just the count of X's so far...
B Rgds,
Mkeiwua