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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi nutnut,

Why not use the COUNT function as per

because the number on the right column is just the count of X's so far...

B Rgds,

nutnutAuthor Commented:
Thanks not sure I understand, can you provide the SQL syntax please
Patrick MatthewsCommented:

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.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

nutnutAuthor Commented:
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!
Valliappan ANSenior Tech ConsultantCommented:
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

I think this will produce better results.
in this example colxa is the one containing X and As.

select 	a.colxa, 
	(select count(colxa) from yourtable where colxa = 'X' and datecolumn <= a.datecolumn) as row_number
from yourtable a
order by a.datecolumn

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
See attached.
nutnutAuthor Commented:
thanks all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.