Query General
in [Server Configuration]
|
Prev: SQL2008 SSMS loses custom serttings
Next: ANN: New version of Database Workbench, the multi-DBMS IDE now available!
From: Muhammad Bilal on 23 Apr 2010 11:26 No still not working. Is there any way that a cursur can be used to do so? Regards, Muhammad Bilal "Erland Sommarskog" wrote: > Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes: > > No its not working it gives the same serial where dates are same . I > > used row_number() in sql server 2008 it works 100% right but as i said i > > am using sql server 2000 so is there any other way to do so. > > Are there are columns in the table? In such case you would need to add > this to the SELECT COUNT(*) subquery, so that it runs over a unique > condition. And, yes, the subquery will be messy. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > . >
From: Erland Sommarskog on 23 Apr 2010 18:03
Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes: > No still not working. Is there any way that a cursur can be used to do so? What does not work? Were you not able to create a query from my outline? A cursor solution is certainly possible, and what worse is: if there are any volumes, it will be a lot faster than the subquery with COUNT (but a lot slower a query using row_number). CREATE TABLE #tmp (id int IDENTITY PRIMARY KEY, name VARCHAR(20),Serial INT,date DATETIME) INSERT INTO #tmp VALUES ('John' ,NULL,'05-04-2009') INSERT INTO #tmp VALUES ('John' ,NULL,'06-08-2009') INSERT INTO #tmp VALUES ('John' ,NULL,'07-04-2009') INSERT INTO #tmp VALUES ('Tony' ,NULL,'11-02-2010') INSERT INTO #tmp VALUES ('Tony' ,NULL,'11-03-2010') go DECLARE @id int, @serial int, @name varchar(20), @old_name varchar(20) DECLARE cur CURSOR STATIC LOCAL FOR SELECT id, name FROM #tmp ORDER BY name, date OPEN cur WHILE 1 = 1 BEGIN FETCH cur INTO @id, @name IF @@fetch_status <> 0 BREAK SELECT @serial = CASE WHEN @name <> @old_name OR @old_name IS NULL THEN 1 ELSE @serial + 1 END UPDATE #tmp SET Serial = @serial WHERE id = @id SELECT @old_name = @name END DEALLOCATE cur SELECT * FROM #tmp ORDER BY name, date go DROP TABLE #tmp -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |