Restarting Row Numbers

Question

Hi can the below be achieved any way using SQL Query,

RowNum-------City -----Name

1----------------Hyd-----Madhu

2--------------------------Kiran

3--------------------------Loki

4-------------Chn-------Seeta

5------------------------Murugan

6------------------------Ram

7------------------------Venki

to be like,

RowNum-------City -----Name------S.No

1----------------Hyd-----Madhu-------1

2--------------------------Kiran---------2

3--------------------------Loki----------3

4-------------Chn-------Seeta---------1

5------------------------Murugan------2

6------------------------Ram-----------3

7------------------------Venki---------4

How to get the S.No num field ?

I tried using Rank Over (Partition by~~~) but that expects atleast a common column to group for row count.

Is the above achievable ? please let me know.

---

--------------------------- Radhai Krish | Golden Age is no more far | --------------------------

Answer 1

How do you want to group ?

Answer 2

DECLARE @tab TABLE(RowNum INT,City VARCHAR(10),Name VARCHAR(10))
INSERT INTO @tab
SELECT 1,'Hyd','Madhu' UNION ALL
SELECT 2,'Hyd','Kiran' UNION ALL
SELECT 3,'Hyd','Loki' UNION ALL
SELECT 4,'Chn','Seeta' UNION ALL
SELECT 5,'Chn','Murugan' UNION ALL
SELECT 6,'Chn','Ram' UNION ALL
SELECT 7,'Chn','Venki' 

SELECT * FROM @tab
SELECT * FROM
(
	SELECT RowNum
		   ,City
		   ,Name
		   ,ROW_NUMBER() OVER(PARTITION BY City ORDER BY RowNum) AS RN
	FROM @tab
)AS a
ORDER BY a.RowNum

---

Narsimha

Answer 3

Try:

select
    row_number() over(order by city, name) as rownum,
    city,
    name,
    row_number() over(partition by city order by name) as sno
from
    T;

Both enumerations could be non-deterministic in case you have duplicate names for specific city.

---

AMB

Some guidelines for posting questions...

Answer 4

How do you want to group ?

Its actually grouping by City. But in our case the city name is appearing only once in the table irrespective of any count of Name values. The successive rows with empty city values are needed to take the city value from the lastly filled in city value until the row gets a value specified city name in it. So i am struggling to serial number it without the city values specifically mentioned in each rows with the DB Table.

---

--------------------------- Radhai Krish | Golden Age is no more far | --------------------------

Answer 5

If the values of column [rownum] are in chronological order, then you can flag first row per each group and then count how many rows are previous to current row (inclusive) with thois flag on. This number will serve as a grouping id.

If you have SS 2012 then it is easier to do this with the new support to the OVER clause.

USE tempdb;
GO
DECLARE @tab TABLE(RowNum INT,City VARCHAR(10),Name VARCHAR(10))
INSERT INTO @tab
SELECT 1,'Hyd','Madhu' UNION ALL
SELECT 2,null,'Kiran' UNION ALL
SELECT 3,null,'Loki' UNION ALL
SELECT 4,'Chn','Seeta' UNION ALL
SELECT 5,null,'Murugan' UNION ALL
SELECT 6,null,'Ram' UNION ALL
SELECT 7,null,'Venki';

WITH S AS (
SELECT
	T.*,
	SUM(R.first_row_in_grp_flag) OVER(
	ORDER BY RowNum
	ROWS BETWEEN unbounded preceding AND current row
	) AS grp
FROM
	@tab AS T
	CROSS APPLY
	(SELECT CASE WHEN T.city IS NOT NULL THEN 1 ELSE 0 END AS first_row_in_grp_flag) AS R
)
SELECT
	rownum,
	city,
	name,
	ROW_NUMBER() OVER(PARTITION BY grp ORDER BY rownum) AS sno
FROM 
	S
ORDER BY
	rownum;
GO
/*

rownum	city	name	sno
1	Hyd	Madhu	1
2	NULL	Kiran	2
3	NULL	Loki	3
4	Chn	Seeta	1
5	NULL	Murugan	2
6	NULL	Ram	3
7	NULL	Venki	4

*/

If you have a version lower than 2012, then a SQLCLR or cursor solution would be better if you have to many rows. I am going to use a correlated subquery but keep in mind that this approach is really slow compared to the ones I mentioned.

USE tempdb;
GO
DECLARE @tab TABLE(RowNum INT,City VARCHAR(10),Name VARCHAR(10))
INSERT INTO @tab
SELECT 1,'Hyd','Madhu' UNION ALL
SELECT 2,null,'Kiran' UNION ALL
SELECT 3,null,'Loki' UNION ALL
SELECT 4,'Chn','Seeta' UNION ALL
SELECT 5,null,'Murugan' UNION ALL
SELECT 6,null,'Ram' UNION ALL
SELECT 7,null,'Venki';

WITH S AS (
SELECT
	T.*,
	R.sno AS grp
FROM
	@tab AS T
	CROSS APPLY
	(
	SELECT SUM(CASE WHEN B.city IS NOT NULL THEN 1 ELSE 0 END) AS sno
	FROM @tab AS B
	WHERE B.rownum <= T.RowNum
	) AS R
)
SELECT
	rownum,
	city,
	name,
	MAX(city) OVER(PARTITION BY grp) AS new_city,
	ROW_NUMBER() OVER(PARTITION BY grp ORDER BY rownum) AS sno
FROM 
	S
ORDER BY
	rownum;
GO

/*

rownum	city	name	new_city	sno
1	Hyd	Madhu	Hyd	1
2	NULL	Kiran	Hyd	2
3	NULL	Loki	Hyd	3
4	Chn	Seeta	Chn	1
5	NULL	Murugan	Chn	2
6	NULL	Ram	Chn	3
7	NULL	Venki	Chn	4

*/

---

AMB

Some guidelines for posting questions...

Answer 6

Try this

	declare @a as table(RN INT, City varchar(10), Name VARCHAR(10))
	insert @a values(1, 'Hyd', 'Madhu'), (2, NULL, 'Kiran'), (3, NULL, 'Loki'),
					(4, 'Chn', 'Seeta'), (5, NULL, 'Murugan'), (6, NULL, 'Ram')	, (7, NULL, 'Venki')

	SELECT	M.RN
			,M.City
			,Name
			,ROW_NUMBER() OVER(PARTITION BY (SELECT TOP 1 City FROM @a T WHERE T.RN <= M.RN AND T.City IS NOT NULL ORDER BY T.RN DESC) ORDER BY M.RN)
	  FROM	@a M

Answer 7

Hi Thanks for the queries,

Those worked in case of different City values, but in case if it is of same city then again there is a problem.

Like,

RowNum-------City -----Name

1----------------Hyd-----Madhu

2--------------------------Kiran

3--------------------------Loki

4-------------Hyd-------Seeta

5------------------------Murugan

6------------------------Ram

7------------------------Venki

to be like,

RowNum-------City -----Name------S.No

1----------------Hyd-----Madhu-------1

2--------------------------Kiran---------2

3--------------------------Loki----------3

4-------------Hyd-------Seeta---------1

5------------------------Murugan------2

6------------------------Ram-----------3

7------------------------Venki---------4

Please dont ask me why can both the set of data can be merged, actually we cant do that for there for few more fields additional to these columns. And this is the requirement. When data having 2 set of data with same city the counting goes from 0 to the max line count value. like

RowNum-------City -----Name------S.No

1----------------Hyd-----Madhu-------0

2---------------Hyd--------Kiran---------1

3---------------Hyd--------Loki----------2

4-------------Hyd-------Seeta---------3

5-------------Hyd--------Murugan------4

6--------------Hyd-------Ram-----------5

7------------Hyd---------Venki---------6

Is there any solution to this type of requirement ? Please let me know THanks !

---

--------------------------- Radhai Krish | Golden Age is no more far | --------------------------

Answer 8

USE tempdb;
GO
DECLARE @tab TABLE(RowNum INT,City VARCHAR(10),Name VARCHAR(10))
INSERT INTO @tab
SELECT 1,'Hyd','Madhu' UNION ALL
SELECT 2,null,'Kiran' UNION ALL
SELECT 3,null,'Loki' UNION ALL
SELECT 4,'Chn','Seeta' UNION ALL
SELECT 5,null,'Murugan' UNION ALL
SELECT 6,null,'Ram' UNION ALL
SELECT 7,null,'Venki' UNION ALL
SELECT 8,'c1','n1' UNION ALL
SELECT 9,'c2','n2' UNION ALL
SELECT 10,'Chn','n3' UNION ALL
SELECT 11,null,'n4' UNION ALL
SELECT 12,null,'n5' UNION ALL
SELECT 13,'c3','n6'


;with cte as
(
	select
		*,row_number() over (partition by case when t.City is not null then 1 end order by t.RowNum) rn
	from @tab t
)
,cte2 as
(
	select
		*,max(c.City) over(partition by case when c.City is null then c.RowNum - c.rn else c.rn end) City2
	from cte c
)
select
	*,row_number() over(partition by c2.City2 order by c2.RowNum) - case when count(c2.City) over (partition by c2.City2) > 1 then 1 else 0 end [S.No]
from cte2 c2
order by c2.RowNum

Answer 9

The solution I posted for versions 2005-2008R2 will give you what you want but is not the best one performance wise.

USE tempdb;
GO
DECLARE @tab TABLE(RowNum int NOT NULL UNIQUE clustered,City VARCHAR(10),Name VARCHAR(10))
INSERT INTO @tab
SELECT 1,'Hyd','Madhu' UNION ALL
SELECT 2,null,'Kiran' UNION ALL
SELECT 3,null,'Loki' UNION ALL
SELECT 4,'Hyd','Seeta' UNION ALL
SELECT 5,null,'Murugan' UNION ALL
SELECT 6,null,'Ram' UNION ALL
SELECT 7,null,'Venki';

WITH S AS (
SELECT
	T.*,
	R.sno AS grp
FROM
	@tab AS T
	CROSS APPLY
	(
	SELECT SUM(CASE WHEN B.city IS NOT NULL THEN 1 ELSE 0 END) AS sno
	FROM @tab AS B
	WHERE B.rownum <= T.RowNum
	) AS R
)
SELECT
	rownum,
	city,
	name,
	MAX(city) OVER(PARTITION BY grp) AS new_city,
	ROW_NUMBER() OVER(PARTITION BY grp ORDER BY rownum) AS sno
FROM 
	S
ORDER BY
	rownum;

-- SS2012
WITH S AS (
SELECT
	T.*,
	SUM(R.first_row_in_grp_flag) OVER(
	ORDER BY RowNum
	ROWS BETWEEN unbounded preceding AND current row
	) AS grp
FROM
	@tab AS T
	CROSS APPLY
	(SELECT CASE WHEN T.city IS NOT NULL THEN 1 ELSE 0 END AS first_row_in_grp_flag) AS R
)
SELECT
	rownum,
	city,
	name,
	MAX(city) OVER(PARTITION BY grp) AS new_city,
	ROW_NUMBER() OVER(PARTITION BY grp ORDER BY rownum) AS sno
FROM 
	S
ORDER BY
	rownum;
GO

/*

rownum	city	name	new_city	sno
1	Hyd	Madhu	Hyd	1
2	NULL	Kiran	Hyd	2
3	NULL	Loki	Hyd	3
4	Hyd	Seeta	Hyd	1
5	NULL	Murugan	Hyd	2
6	NULL	Ram	Hyd	3
7	NULL	Venki	Hyd	4

*/

---

AMB

Some guidelines for posting questions...

Answer 10

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. Start over and be polite; do not post another useless silly ASCII Picture. It implies that the table has a mixed structure. That is impossible in RDBMS. 

And why do you think that there is a concept of a "row number" in RDBMS??  Sets have no ordering by definition!!  Columns are nothing like fields; rows are not columns; tables are not sequential files. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 11

Radhai

There are different solutions to the type of problem you have but with this type of schema and queries, you are not doing what SQL does best (SET Based operations) and the design is also not optimal. I would suggest that if you can take a relook at the schema/data, please do so for sake of optimal performance.

Answer 12

Here you go for your changed queries for both scenarios

declare @a as table(RN INT, City varchar(10), Name VARCHAR(10))
	insert @a values(1, 'Hyd', 'Madhu'), (2, NULL, 'Kiran'), (3, NULL, 'Loki'),
					(4, 'Hyd', 'Seeta'), (5, NULL, 'Murugan'), (6, NULL, 'Ram')	, (7, NULL, 'Venki')

	SELECT	M.RN
			,M.City
			,Name
			,ROW_NUMBER() OVER(PARTITION BY (SELECT TOP 1 T.RN FROM @a T WHERE T.RN <= M.RN AND T.City IS NOT NULL ORDER BY T.RN DESC) ORDER BY M.RN)
	  FROM	@a M
go


declare @a as table(RN INT, City varchar(10), Name VARCHAR(10))
	insert @a values(1, 'Hyd', 'Madhu'), (2, NULL, 'Kiran'), (3, NULL, 'Loki'),
					(4, 'Chn', 'Seeta'), (5, NULL, 'Murugan'), (6, NULL, 'Ram')	, (7, NULL, 'Venki')

	SELECT	M.RN
			,M.City
			,Name
			,ROW_NUMBER() OVER(PARTITION BY (SELECT TOP 1 T.RN FROM @a T WHERE T.RN <= M.RN AND T.City IS NOT NULL ORDER BY T.RN DESC) ORDER BY M.RN)
	  FROM	@a M