records with their row numbers in a table
-
Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table. Like we use Rownum in Oracle. Thank you. Regards, Mohd. Abdul Aleem,
S/W Engineer Akebono Soft Technologies aleem_abdul@akebonosoft.com.
indian143 wrote:
Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table.
You need SQL Server 2005. However, you should realise that row numbers are a fiction. Oracle and SQL Server 2005 supply a number based on the order that the rows are returned and have little (only incidental) or no bearing on how the data is actually stored. Anyway, the answer is ROW_NUMBER[^]
Upcoming events: * Glasgow: Geek Dinner (5th March) * Edinburgh: Web Security Conference Day for Windows Developers (12th April) My: Website | Blog | Photos
-
indian143 wrote:
Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table.
You need SQL Server 2005. However, you should realise that row numbers are a fiction. Oracle and SQL Server 2005 supply a number based on the order that the rows are returned and have little (only incidental) or no bearing on how the data is actually stored. Anyway, the answer is ROW_NUMBER[^]
Upcoming events: * Glasgow: Geek Dinner (5th March) * Edinburgh: Web Security Conference Day for Windows Developers (12th April) My: Website | Blog | Photos
-
Could you please tell me that dont I have any other option to get it SQL Server 2000. Please. Thank you. Regarsd,
S/W Engineer Akebono Soft Technologies aleem_abdul@akebonosoft.com.
I do not recommend this but you could create a insert/delete trigger that loops through the recordset and inserts the new value. This is a performance hit. A sample trigger is below. You must have a primary key on the table to do this. I am confused of why you need to reference the rownumber. It has no significance. If you dont mind a few holes here and there after you have deleted a record, use an Identity column.
CREATE TRIGGER dbo.trg_Count ON dbo._TEST AFTER INSERT, DELETE AS BEGIN SET NOCOUNT ON; DECLARE @PKEY UNIQUEIDENTIFIER DECLARE @I BIGINT IF ((SELECT trigger_nestlevel()) = 1) BEGIN DECLARE TRG_TEST_CUR CURSOR FOR SELECT [GUID] FROM _TEST OPEN TRG_TEST_CUR FETCH NEXT FROM TRG_TEST_CUR INTO @PKEY SET @I = 1 WHILE @@FETCH_STATUS = 0 BEGIN UPDATE _TEST SET ID = @I WHERE [GUID] = @PKEY SET @I = @I + 1 FETCH NEXT FROM TRG_TEST_CUR INTO @PKEY END CLOSE TRG_TEST_CUR DEALLOCATE TRG_TEST_CUR END END GO