Auto counting in SQL

Hi all,

I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it.

So if I for example select all Dates older than today's date, I would want something like this:

110/12/2006210/18/2006310/20/2006410/22/2006510/30/2006

Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually".

Is this possible? And if yes, how ? :)

Thanks in advance

Nick

See if this helps:

Declare @.TTable ( col1datetime)Insert into @.TSelect'10/12/2006'unionallSELECT'10/18/2006'unionallSELECT'10/20/2006'unionallSELECT'10/22/2006'unionallSELECT'10/30/2006'SELECTCOUNT(*)AS [Row Number], T1.col1FROM @.T T1, @.T T2WHERE T1.col1 >= T2.col1Group by T1.col1

|||

SELECT *,row_number() OVER (ORDER BY ...) AS [Row Number]

FROM ...

ORDER BY ...

Just fill in the ...'s

|||

ndinakar, thanks for the answer, but not quite what i was looking for

Motley, thanks :) that was exactly what I needed ;)

cheers!

|||

I still don't get it how does this work ... an example:

If I return the values from DB using the following SQL

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTable) AS DerivedTable

it works perfectly. When I add some filtering to WHERE-part

WHERE MyColB <> 'value1' AND MyColB <> 'value2'

I get somewhat strange results. Row numbering in my case starts from row 57, second row jumps to 60, then go normally for couple of rows, then jumps to 77 and so on. So it's definately not sequential. Any ides for this ?

|||

You should make sure that your where is in the correct SELECT statement.
There is for example a big difference in results when used either

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTable) AS DerivedTableWHERE MyColB <> 'value1' AND MyColB <> 'value2'

in stead of

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTableWHERE MyColB <> 'value1' AND MyColB <> 'value2') AS DerivedTable

in the first statement, you're likely to get non-sequential numbering, and in the second one you should get them sequentially

|||

Yes, of course, I didn't realize that, but now it works. Thanks