Wednesday, August 17, 2011

Blog Moved

My blog has been moved to

Link







Thursday, July 22, 2010

Get Top Last Record

This query will help you get the top most rows from the bottom. It may sound simple but complex, when you are not allowed to use the Primary key or unique key.

There may be many other ways..But I thought of this one way.
Note: VName is th column name of table mytable

--this query will give data from the last record
select * from mytable
Declare @NoOfRecord int
Set @NoOfRecord=2 --1 gives the last record --2 will give second last record

Select Top 1 VName from mytable
where VName not in (
select Top
(Select (Count(*)-@NoOfRecord) from mytable)
VName
from mytable
)

Wednesday, May 12, 2010

Swap Values in SQL

There is a way to swap bit values.I saw it in one of my colleagues mahendra blog.

Later I thought if I want to swap non bit values. May it be a string or an integer. Then how to do it. There may be numerous ways. But one way which I tried is as below.

Step 1 . Make a table named data and have two columns id(pk autonumber) and val(int)

For testing put come data
Step 2. Put some raw data eg, 1,1;2,2;3,1;4,2;5,1;6,2 etc.

Step 3.
create a procedure names uspSwapData

Create procedure uspSwapData
as
declare @mydata table
(
id int)

insert into @mydata
Select ID from data where val=2

update data set val=2 where
ID in (select id where val=1)
-- or you can use this query instead of the above one
-- update data set val=2 where val=1

update data set val=1 where id in (select id from @mydata)


Now it is done. you will get the Val swapped with 1 and 2

Enjoy ... I hope this will help many SQL loving souls...