Show Row number in query result in MSSQL Server 2005

SELECT row_number() OVER (ORDER BY DeliveryLocationCode) AS Row_No,DeliveryLocationCode, ContactPerson
FROM DeliveryLocationMaster

Comments

Post a Comment

Popular posts from this blog

SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time. Here I have created a simple Function for this. Create Function Trim(@mText varchar(MAX)) Returns varchar(MAX) AS Begin return LTRIM(RTRIM(@mText)) End You can run this function as shown below here, Select dbo.Trim(' Test ') So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.
Read more

Delete duplicate records from a Table in SQL Server

drop table ##temp create table ##temp (id char(3) ,marks int ) create table ##temp2 (id char(3) ,marks int ) insert into ##temp(id,marks) ----Here we are inserting duplicate select '001',50 ----records for each ID union all select '001',60 union all select '002',66 union all select '002',88 union all select '003',92 union all select '003',64 union all select '004',44 union all select '005',67 ----Here we are getting the distinct records and insert then into another Tempory table insert into ##temp2 select distinct id,max(marks) from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) group by id ---And delete those duplicate records from original Table delete from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) ---And again inser the inserted reocrds from temporary Table insert into ##t...
Read more

Procedure for Split the words in SQL Sever

Image
Here it is the procedure to Split the words using comma seperator. still you can use different character for split instead of comma(','). Here i m using 'E,l,e,p,h,a,n,t' as word with comma characters. so the output should be 'E','l','e','p','h','a','n','t'. Declare @name as varchar(20) Declare @i as int Declare @char as char Declare @word as varchar(20) select @name='E,l,e,p,h,a,n,t' set @word='' set @i=1 while @i ',' begin set @word=@word+@char end else if (@char=',' and @i len(@name)) begin print @word set @word='' end ---Print the last word if @i=len(@name) begin print @word end set @i=@i+1 end Output of this query would be,
Read more