Blog Moved

My blog has been moved to

www.ketans.com

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
)

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...