ROW COUNTER COLUMN

ricespn · October 19th, 2006, 12:35 PM

Hi,

How can I SELECT all the records of a table but having the first column with the row number as in excel or query analyzer.

I have this but it goes to a new table and I wanted in the same query.

SELECT IDENTITY(int, 1,1) AS IDNUMBER
INTO NUMBERS
FROM MYTABLE

out come:

IDNUMBER
1
2
3
4
5...

Nedded:

IDNUMBER NAME LASTNAME
1 joe smith
2 alex gun
3...

Strange and crazy, but everything is possible

rstelma · October 19th, 2006, 03:26 PM

If you make an identity column in the new table, on the INSERT it will create what you're looking for. Create an identity column with an identity seed of 1 and increment of 1. Just ignore the new table's column on the INSERT

INSERT INTO [New Table] (Col1, Col2, Col3) (SELECT colx, coly, colz from [Old Table])

Jeff Mason · October 19th, 2006, 04:33 PM

There is a way to do this without adding an identity column to the table, or a creating a temporary table. That having been said, it's very likely that it's considerably faster to do it that way, depending on the size of the table.

First, if you want to assign row numbers, there needs to be some way to distinguish one row from another. Further, a row number implies some sort of ordering on the results. Otherwise, why did you decide to assign, say, row number 2 to that particular row?

So, we need some column to order on.

What we'll do is to partition the table into subsets, where each subset contains all the rows whose sort value is less than (or equal to) the sort column value for each row in turn. The row index, then, is the count of the number of rows in the subset constructed for each row.

A correlated subquery allows us to do this:

Code:

SELECT (SELECT COUNT(*)
          FROM yourtable T2
         WHERE T2.sortcolumn <= T1.sortcolumn) as RowIndex, T1.*
FROM yourtable T1
ORDER BY RowIndex;

Note that this technique doesn't handle ties well at all, as rows with the same sortcolumn value will get the same RowIndex value. But what do ties mean? If I have two rows with the same sortcolumn value, which one gets which RowIndex value??

Jeff Mason
Custom Apps, Inc.
[email protected]

ricespn · October 20th, 2006, 07:51 AM

Thanks Jeff!

:D It works just fine..

Quote:

quote:Originally posted by Jeff Mason
There is a way to do this without adding an identity column to the table, or a creating a temporary table. That having been said, it's very likely that it's considerably faster to do it that way, depending on the size of the table.

First, if you want to assign row numbers, there needs to be some way to distinguish one row from another. Further, a row number implies some sort of ordering on the results. Otherwise, why did you decide to assign, say, row number 2 to that particular row?

So, we need some column to order on.

What we'll do is to partition the table into subsets, where each subset contains all the rows whose sort value is less than (or equal to) the sort column value for each row in turn. The row index, then, is the count of the number of rows in the subset constructed for each row.

A correlated subquery allows us to do this:

Code:

SELECT (SELECT COUNT(*)
          FROM yourtable T2
         WHERE T2.sortcolumn <= T1.sortcolumn) as RowIndex, T1.*
FROM yourtable T1
ORDER BY RowIndex;

Note that this technique doesn't handle ties well at all, as rows with the same sortcolumn value will get the same RowIndex value. But what do ties mean? If I have two rows with the same sortcolumn value, which one gets which RowIndex value??

Jeff Mason
Custom Apps, Inc.
[email protected]

=======================
Strange and crazy, but everything is possible

ricespn · October 20th, 2006, 07:53 AM

rstelma Thanks..

I try that the only thing is that I didn't want to create a new table I just want it to be in a resulset...:D

Quote:

quote:Originally posted by rstelma
If you make an identity column in the new table, on the INSERT it will create what you're looking for. Create an identity column with an identity seed of 1 and increment of 1. Just ignore the new table's column on the INSERT

INSERT INTO [New Table] (Col1, Col2, Col3) (SELECT colx, coly, colz from [Old Table])

=======================
Strange and crazy, but everything is possible

Jeff Moden · October 20th, 2006, 06:36 PM

Ricespn,

Use a temporary table, instead... same code... just use a temp table...

--Jeff Moden