December 11, 2002 at 2:11 pm
What is the simplest (and efficient) way to get a unique number for each row in my Select Query? Sybase has a wonderful Number() function to do this, numbering 1 to N for the rows returned. I can't figure out a simple way to do this in SQL Server. It would be nice if the numbers were consecutive, ending with the count, but that's not necessary.
December 11, 2002 at 2:22 pm
There are two ways that I know of:
Method 1: Create a new table insert all the rows. Make sure the new table has and identity column with a seed value of 1 that increments by 1. Something like this:
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable
Method 2:
select count(*), a.name from sysobjects a, sysobjects b
where
a.name >= b.name
group by a.name
order by 1
This is fairly slow for large tables.
ALSO LOOK AT ARTICLE:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q186133 -How to
Dynamically Number Rows in a Select Statement
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 11, 2002 at 2:41 pm
yeah, I figured either of those would come back as responses. I was hoping to avoid the temp table, and the size of the data would make the "double-table group" option a bad choice. I'm thinking of just using NewID() to 'generate' a GUID, but I'm not sure the programming framework I'm returning the results to can handle a non-integer ID column
December 11, 2002 at 5:03 pm
I spke with another person in a thread about this and this was the only concept I could come up with that might work but I haven't built it yet.
CREATE TABLE tbl_InLineNumber (
UniID [uniqueidentifier] NOT NULL PRIMARY KEY CLUSTERED,
NumVal [int] NOT NULL,
StartedOn [datetime] NOT NULL CONSTRAINT [df_user_date] DEFAULT getdate())
GO
Create a C++ exteneded stored procedure that accepts 1 value [uniqueidentifier] and returns an [int] in the code it opens a connection to SQL server and an SP we will create. It does a search for the uniqueidentifier we created and does an update thru an SP like so
CREATE PROCEDURE ip_GetNum
@UnidID uniqueidentifier,
@NumVal int OUTPUT
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM tbl_InLineNumber WHERE UniID = UniID)
BEGIN
UPDATE tbl_InLineNumber SET @NumVal = NumVal, NumVal = NumVal + 1 WHERE UniID = @UniID
ELSE
BEGIN
SET @NumVal = 1
INSERT tbl_InLineNumber (UniID, NumVal) VALUES (@UniID, 1)
END
GO
Then we wrap our Extended SP in a function to accept a uniqueidentifier and return an INT.
Finally we do something like so with what you need.
DECLATE @uniid uniequeidentifier
SET @uniid = NEWID()
SELECT ....
(select*,
yourselect using the function wrapping the xp to get the row number in this sub select to get with a item value) where itemval BETWEEN 1 and n
DELETE tbl_InLineNumber WHERE UniID = @uniid
Of course this is all theory code and quite an awkward way to do. But until I write and test have not idea of true feasibility or performance. I will try to do in the next few weeks as I am curious about.
December 11, 2002 at 8:42 pm
Basically, this is a missing feature of SQL server.
Lot's of ideas but they all boil down to this.
December 11, 2002 at 9:32 pm
For anyone who can stand the overhead of a temp table and dual selects, here's a template function that gives you row ID in your query. You can use this to get paging simply (at least to YOUR output query). Just be aware that you would need to code this function for EVERY select statement you need the ROWID() column on.
Create function dbo.RowWithRowID()
returns @Rows table
(RowID INTEGER NOT NULL Identity (1,1)
,Col1 varchar(50) not null
,col2 varchar(20) not null
)
as
begin
insert @Rows
select cast(Guid as varchar(50))
, cast(isNull(Email, 'Missing') as varchar(20))
from table1
return
end
Edited by - don1941 on 12/11/2002 9:33:23 PM
December 12, 2002 at 6:29 am
Interesting Idea using a function. How much less overhead is this over building a temp table? Done any benchmarks?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 13, 2002 at 12:09 pm
The best thing about using the function is that you can simply replace the original table with the function in your select statement. Also you could add parameters for the where clause to the function so your numbering comes out correct.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply