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
nutnutAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
I think this will produce better results.
in this example colxa is the one containing X and As.
 

select 	a.colxa, 
	a.datecolumn, 
	(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

0
 
mkeiwuaCommented:
Hi nutnut,

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
0
 
nutnutAuthor Commented:
Thanks not sure I understand, can you provide the SQL syntax please
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Patrick MatthewsCommented:
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
0
 
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!
0
 
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

Thanks
0
 
awking00Connect With a Mentor Commented:
See attached.
query.txt
0
 
nutnutAuthor Commented:
thanks all
0
All Courses

From novice to tech pro — start learning today.