reset row number/identity based on another field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

reset row number/identity based on another field?

Author

Topic

gypsymami
Starting Member

3 Posts

Posted - 2007-12-19 : 17:32:45

I'm not sure if this is even possible but if so, please help!

I need a counter on a table that will reset based on another field. Basically, I would like the row count (id field, whatever the name) to return to 1 when the ID field changes. What I am trying to do is count the number of dates that is associated with each ID.

Right now, I have this:
Row Date ID
1 2007-09-01 00:00:00.000 277
2 2007-10-01 00:00:00.000 277
3 2007-11-01 00:00:00.000 277
4 2007-12-01 00:00:00.000 277
5 2008-01-01 00:00:00.000 277
6 2007-10-01 00:00:00.000 293
7 2007-11-01 00:00:00.000 293
8 2007-12-01 00:00:00.000 293
9 2008-01-01 00:00:00.000 293

What i would LIKE is this:
Row Date ID
1 2007-09-01 00:00:00.000 277
2 2007-10-01 00:00:00.000 277
3 2007-11-01 00:00:00.000 277
4 2007-12-01 00:00:00.000 277
5 2008-01-01 00:00:00.000 277
1 2007-10-01 00:00:00.000 293
2 2007-11-01 00:00:00.000 293
3 2007-12-01 00:00:00.000 293
4 2008-01-01 00:00:00.000 293
... and so on

I'm using the SQL below because that's the only way i could figure to get the counter in there. Of course, it doesn't give me what a want
drop table #table
select identity (int,1,1) as row , date,id
into #table
from [TableList]
where date is not null and ID in ( 277, 293)
order by date
select * from #table group by id, ratingdate, row order by ratingdate

Any assistance would be greatly appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 17:44:01

Fourth time today!

-- Prepare sample data
DECLARE	@Sample TABLE (Row INT, Date DATETIME, ID INT)

INSERT	@Sample
SELECT	1, '2007-09-01', 277 UNION ALL
SELECT	2, '2007-10-01', 277 UNION ALL
SELECT	3, '2007-11-01', 277 UNION ALL
SELECT	4, '2007-12-01', 277 UNION ALL
SELECT	5, '2008-01-01', 277 UNION ALL
SELECT	6, '2007-10-01', 293 UNION ALL
SELECT	7, '2007-11-01', 293 UNION ALL
SELECT	8, '2007-12-01', 293 UNION ALL
SELECT	9, '2008-01-01', 293

-- Initialize staging area
DECLARE	@Stage TABLE (RowID INT IDENTITY(1, 1), Date DATETIME, ID INT)

INSERT		@Stage
SELECT		Date,
		ID
FROM		@Sample
ORDER BY	ID,
		Date

-- Show the expected output
SELECT		s.RowID - e.MinID + 1 AS Row,
		s.Date,
		s.ID
FROM		(
			SELECT		ID,
					MIN(RowID) AS MinID
			FROM		@Stage
			GROUP BY	ID
		) AS e
INNER JOIN	@Stage AS s ON s.ID = e.ID
ORDER BY	s.ID,
		s.RowID

E 12°55'05.25"
N 56°04'39.16"

gypsymami
Starting Member

3 Posts

Posted - 2007-12-19 : 18:12:08

I may be misunderstanding something - or maybe not explaining correctly. But, the table has THOUSANDS of IDs and Thousands of dates. From the looks of this, i'd basically be manually writing the table. Correct?

I don't want to do this. I am hoping to find a way to change the counter based on the ID field.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 18:15:15

I don't think I have access to your database. Am I right?
If I don't have access to your database, I must somehow have some sample data, right?

So I wrote the "Prepare sample data" part in the code above since I do not have access to your database.

What you need, is the "Initialize staging area" and "Show the expected output" parts.
Copy those two parts, change the table names and column names to the one used in your environment.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 18:16:27

[code]-- Initialize staging area
DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), Date DATETIME, ID INT)

INSERT @Stage
SELECT Date,
ID
FROM {Your table name here}
ORDER BY ID,
Date

-- Show the expected output
SELECT s.RowID - e.MinID + 1 AS Row,
s.Date,
s.ID
FROM (
SELECT ID,
MIN(RowID) AS MinID
FROM @Stage
GROUP BY ID
) AS e
INNER JOIN @Stage AS s ON s.ID = e.ID
ORDER BY s.ID,
s.RowID[/code]

E 12°55'05.25"
N 56°04'39.16"

gypsymami
Starting Member

3 Posts

Posted - 2007-12-20 : 11:33:13

beautiful. Thanks a million!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 11:54:14

You're welcome.
Please come back when you experience next difficulty.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources