There is a common requirement to show records with serial number in Grid. The common scenario is, we take template field and label in Item Template. In Row_DataBound we can generate serial number using variable which is increamnted each time. The another approach is to genarate this column in SQL itself. Yes you can do that..! Here is a Query for that.
SELECT ROW_NUMBER() OVER (ORDER BY ColumnName1) As SrNo, ColumnName1, ColumnName2 FROM TableName
Fig- (1) Query to display Serial Number Column using SQL Server
Here is the result of the Query.
Happy Programming !
it’s not workin in SQL Server 2000.
is there any solution?
Upen,
You can do that as shown in below article,
http://www.mutuallybeneficial.com/index_files/RowNumber_2005.htm
I assume that I have a table called Emp with two columns Id (identity false) and Name which contains data Like
1 kamal
2 kalyan
3 kishore
4 sipu
5 deepu
6 ishan
some body deleted the record 4 sipu then i want to reserialize the records
Like
1 kamal
2 kalyan
3 kishore
4 deepu
5 ishan
declare @N Varchar(50);
create table #TestData(
id int identity,
name varchar(250))
declare A Cursor for
select Name from emp
open A
FETCH NEXT FROM A
INTO @N
WHILE @@FETCH_STATUS =0
Begin
insert into #TestData(Name) values(@N)
FETCH NEXT FROM A
INTO @N
End
CLOSE A
DEALLOCATE A
delete from emp
insert into emp select * from #TestData
drop table #TestData
Thanks for putting this information on net.
God bless you guys… appreciate the knowledge
Also, more simpler way is the following:
Identity(data type, start, increment by) As Name
Example: Identity(Int, 1 ,1) As SN
What if i want result order by firstname or last name
Is there a way to do this in Oracle Sql Query. Showing serial numbers by side of the records
I have tried rownum, but it does not give the desired output.
It does not work with Ms sql Server. Please assist
Emma,
I have tested this query in SQL Server 2005 and it is working fine.
Chiragrdarji,
thank you for your post.
Saved me some time and fussing around with a cursor.
I used your hint to insert continuous numbers above the highest number into an existing domain value table where the type of domain is the first column and the numerical domain value the second column.
Something like:
INSERT INTO T1 (C1, C2, C3, C4)
SELECT
1234,
(SELECT MAX(C2) FROM T1 WHERE C1 = 1234) + ROW_NUMBER() OVER(ORDER BY C2),
T2C3,
‘Domain Value automatically inserted.’
FROM T2
Works perfectly!
Hi…chirag this is dhaval patel from UVPCE,kherva, i have seen the above photo and remembered you…
I am from 2002-2006 batch…CE…how r u doing ? r u from the same batch of Amar Shah…
how r udoing?
where r u?
let me know your contact details…
mine is..
Mail me on dhavalpatel02@yahoo.com
Dhaval
Ahmedabad
hi chirag,
damn cool way to do it man… works swell in SQL 2005… thanks
Thanks…!!! Chirag….
Appriciate your knowledge, solved my isssue partially.
Thanks again,
Regards,
Chintesh Soni
THANKS A LOT FOR MY PROBLEM SOLVED.
I APPRICATE UR KNOWLEDGE
VIJAY
TAMILNADU
Its a nice article and sorted my problem
..
There are other pseudo columns as well that we can use.
Thanks
I am trying this query but it will give an error
‘ROW_NUMBER’ is not a recognized function name.
SELECT ROW_NUMBER() OVER (ORDER BY ColumnName1) As SrNo, ColumnName1, ColumnName2 FROM TableName
ROW_NUMBER() Not supporting on MS SQL Server 2000…….Can u sent MS SQL Server 2000 compatable.
thank you so much…. very good effort.. keep rocking….
Hi Its not working in MySQL.
How to overcome this problem???
DEEPA SRINIVAS
TAMILNADU
Thanks it solved the problem……,
Rgds,
arun
Thanks,Solved my problem