Next Row - 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

General SQL Server Forums

New to SQL Server Programming

Next Row

Author

Topic

chennaraaj
Starting Member

17 Posts

Posted - 2013-07-02 : 06:37:22

Hi,

i have a three columns like col1,col2 ,col3 with data

col1 col2 col3

2 NDT Inspection 1
4 M End Inspection 2
6 BO Inspection 3
8 Hydro Inspection 4
10 FET Inspection 5
12 ODP Inspection 6
14 Stencilling Inspection 7
15 Final Inspection 8

in that above date ..if i pass the col2 ='ODP Inspection', i get the col1 value = 12 ......but i need to display the previous result like

col1 col2 col3

10 FET Inspection 5

rk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 06:45:16

Assuming col3 will always be sequential you can do this


SELECT t2.*
FROM table t1
INNER JOIN table t2
ON t2.col3 = t1.col3 - 1
WHERE t1.col2 ='ODP Inspection'

In case col3 has gaps. you need this


;With CTE
AS
(
select *,row_number() over (order by col3) as seq
from table
)
SELECT t2.col1,t2.col2,t2.col3
FROM CTE t1
INNER JOIN CTE t2
ON t2.seq = t1.seq - 1
WHERE t1.col2 ='ODP Inspection'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

chbala85
Starting Member

49 Posts

Posted - 2013-07-02 : 06:59:44

Try . It will work........

select top 1 * from (
select top 2 * from (
select * from table1
except
select * from table1 where userId >=12
) as test order by col1 desc
) as test1n order by col1 asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources