Process Range IDs - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Process Range IDs

Author

Topic

dsegalles80
Starting Member

3 Posts

Posted - 2008-09-02 : 16:33:13

Hello everyone,


DECLARE @MyTable TABLE 
(
	RowID bigint,
	RowKey int,
	Locale varchar(10),
	Descr nvarchar(1000)
)

INSERT INTO @MyTable (RowID,RowKey,Locale,Descr)
 SELECT '1','5','en','Sample Description 1' UNION ALL
 SELECT '2','5','de','Sample Description 2' UNION ALL
 SELECT '3','4','en','Sample Description 3' UNION ALL
 SELECT '4','3','en','Sample Description 4' UNION ALL
 SELECT '5','2','en','Sample Description 5' UNION ALL
 SELECT '6','1','en','Sample Description 6' UNION ALL
 SELECT '7','1','de','Sample Description 7' UNION ALL
 SELECT '8','2','en','Sample Description 8' UNION ALL
 SELECT '9','3','en','Sample Description 9' UNION ALL
 SELECT '10','5','en','Sample Description 10' UNION ALL
 SELECT '11','4','en','Sample Description 11' UNION ALL
 SELECT '12','3','en','Sample Description 12' UNION ALL
 SELECT '13','2','en','Sample Description 13' UNION ALL
 SELECT '14','1','en','Sample Description 14' UNION ALL 
 SELECT '15','3','de','Sample Description 15' 
 

SELECT * FROM @MyTable

In the above data, How do I get the range of RowIDs in such a way that there will be no duplicate keys(RowKey) in a certain RowID range?

I need to check both the [RowKey] and [Locale] columns combination in getting the range.
So the above data shall output/result the following range:


RowID
 8
 12
 16

So the range from 1-8 will cover only the following range of data with no duplicate based on the column
combination [RowKey] & [Locale]:


RowID     RowKey    Locale      Descr
 1		5		en		Sample Description 1
 2          5		de		Sample Description 2
 3		4		en		Sample Description 3
 4		3		en		Sample Description 4
 5		2		en		Sample Description 5
 6		1		en		Sample Description 6
 7		1		de		Sample Description 7

then the range of 8-12 will cover only the following range of data:


RowID     RowKey    Locale      Descr
 8		2		en		Sample Description 8
 9          3		en		Sample Description 9
 10		5		en		Sample Description 10
 11		4		en		Sample Description 11

then the range of 12-16 will cover only the following range of data:


RowID     RowKey    Locale      Descr
 12		3		en		Sample Description 12
 13         2		en		Sample Description 13
 14		1		en		Sample Description 14
 15		3		de		Sample Description 15

You may be wondering why there is RowID 16 in the output, the reason is that I need to get the MAX(RowID)+1 to cover all the range needed.

Anyone who knows how to get these range ids?

thank you very much for your help.

- dans

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources