help for sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help for sql query

Author

Topic

vijayabalajicse
Starting Member

5 Posts

Posted - 2013-10-05 : 10:10:05

<pre>
table
names
-----
abu
arun
sugan
suhe
sagu
viru
dhone
goru

<u> Sql query for above output </u>

previous_name current_name Lastname
null abu arun
abu arun sugan
arun sugan suhe
sugan suhe sagu
suhe sagu viru
sagu viru dhone
viru dhone goru
dhone goru null
</pre>

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-10-06 : 04:20:00

what is your question ?

KH
[spoiler]Time is always against us[/spoiler]

vijayabalajicse
Starting Member

5 Posts

Posted - 2013-10-06 : 05:24:42

table

names
-----
abu
arun
sugan
suhe
sagu
viru
dhone
goru

What query do I need to obtain the following output?

previous_name current_name Lastnam
null abu arun
abu arun sugan
arun sugan suhe
sugan suhe sagu
suhe sagu viru
sagu viru dhone
viru dhone goru
dhone goru null

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-06 : 17:14:32

quote:
Originally posted by vijayabalajicse

<pre>
table
names
-----
abu
arun
sugan
suhe
sagu
viru
dhone
goru

<u> Sql query for above output </u>

previous_name current_name Lastname
null abu arun
abu arun sugan
arun sugan suhe
sugan suhe sagu
suhe sagu viru
sagu viru dhone
viru dhone goru
dhone goru null
</pre>

If your source table has only the one column that you have indicated, there is no way you can get the output you are looking for. That is because the data in a table is an unordered collection. So SQL Server does not know that abu comes first, or that arun follows it or any other information about the ordering. Unless you have some other column that specifies how these names are to be ordered, you cannot get the output you are looking for.

vijayabalajicse
Starting Member

5 Posts

Posted - 2013-10-06 : 21:40:34

SELECT
LAG(names) OVER (ORDER BY names) Previousnames,
names,
LEAD(names) OVER (ORDER BY names) Nextnames
FROM yourTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources