Row number
-
Hi I need a new row number (any integer) as part of my result set.
For example:
Select rownum(), Addr,name from Person
----
1 NewYork abc
2 CA DEF
----
I want to avoid temp tables..trying to get in a simple select if possible from system functions.
Thanks
-
Which version of SQL server ? SQL2000 or SQL2005 ?
-
Not sure, but is this what you want?
DECLARE @Person TABLE( PK integer, Addr varchar(10), [name] varchar(3))
INSERT INTO @Person
SELECT 1, 'NewYork', 'abc'
UNION
SELECT 2, 'CA', 'DEF'
SELECT P1.PK, COUNT(*) AS RowNumber, P1.Addr, P1.[name]
FROM @Person P1
INNER JOIN @Person P2 ON( P1.PK = P2.PK)
WHERE P1.Addr >= P2.Addr
GROUP BY P1.PK, P1.Addr, P1.[name]
I wasn't born stupid - I had to study.
-
Sql server 2000
-
I want to avoid temp tables..trying to get in a simple select if possible from system functions. Can you give an example of what you mean by "from system functions"?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
-- First, simulate your table...
DECLARE @Person TABLE
( Address VARCHAR(29), Name VARCHAR(20) )
INSERT INTO @Person (Address,Name)
VALUES ('NewYork','abc')
INSERT INTO @Person (Address,Name)
VALUES ('CA','DEF')
-- Next, create and populate a table variable with a RowID field
DECLARE @PersonWithCounter TABLE
( RowID INT, Address VARCHAR(29), Name VARCHAR(20) )
INSERT INTO @PersonWithCounter
( Address,Name)
SELECT Address = Address, Name = Name
FROM @Person
-- Next, populate your RowID field
DECLARE @RowID INT
SET @RowID = 0
UPDATE @PersonWithCounter
SET @RowID = RowID = @RowID + 1
-- Finally, look at the results
SELECT * FROM @PersonWithCounter
-
You could use something like the following - although you said you want to avoid use of temp tables, if the table is quite big and you are using a stored proc, I would expect use of a table var to be a better option.
What are you using the number for though? Don't forget that the DB won't guarantee the order of results returned if no order by is specified.
set
nocount on
-- make some source data
declare @Person table(City varchar(20), Code char(3))
insert into @Person values ('NewYork', 'abc')
insert into @Person values ('CA', 'DEF')
insert into @Person values ('Miami', 'MIA')
insert into @Person values ('Boston', 'BOS')
select
* from @Person
-- use table var with auto-id
declare @RowPerson table(Row int identity(1,1), City varchar(20), Code char(3))
insert into @RowPerson select * from @Person order by City
select * from @RowPerson
-- use count with a potentially large join
select count(*) Row, A.City, A.Code
from @Person A
join @Person B
on B.City <= A.City
group by A.City, A.Code
order by 1
set
nocount off
Jon
-
malcome,
That last part of your script (shown below) is interesting. Hard to envision how that works, but it does. Anyone care to explain. Thanks.
DECLARE @RowID INT
SET @RowID = 0
UPDATE @PersonWithCounter
SET @RowID = RowID = @RowID + 1
smv929 -
Here you are setting the value of @RowID to the value of the RowID column. The Value of the RowID column is being set to @RowID + 1
SET @RowID=0 -- the initilization of the variable value
SET @RowID (value is 0 on first row) = RowID (not populated yet) = @ RowID (0) + 1
The above causes the first row to have a value of 1 while also setting the value of the variable to 1. The next row then increments the 1 to a 2 and so on.
-
Thanks. That made it crystal clear. (It's monday.)
smv929
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply