selecting previous and next value

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)

selecting previous and next value

Author

Topic

ames
Starting Member

7 Posts

Posted - 2011-08-30 : 01:10:31

HI I have the following dataset(i have other columns in the table but this is the column contains the values I need it). How can I return the value if previous and next value is same, one up one down value of the blank rows. It should return the value if 27940=27940 else it should return null.
ID
38760
38760
40519
40519
40519
35208
35776
35776
35776
27940
27940
27940
27940

27940
40519
40519

Thank you.

ames
Starting Member

7 Posts

Posted - 2011-08-30 : 01:12:55

One more addition, it should return the value 27940 if previous and next are equal.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-08-30 : 01:30:52

quote:
Originally posted by ames

HI I have the following dataset(i have other columns in the table but this is the column contains the values I need it). How can I return the value if previous and next value is same, one up one down value of the blank rows. It should return the value if 27940=27940 else it should return null.
ID
38760
38760
40519
40519
40519
35208
35776
35776
35776
27940
27940
27940
27940

27940
40519
40519

Thank you.

one up one down value of the blank rows.
What do you mean by this?

Karthik
http://karthik4identity.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-30 : 04:50:06

How do you determine what is "previous" and "next"?
Do you have an identity column too?

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-30 : 04:54:01

remember there is no inherent order. Tables have no order full stop and queries have no *guaranteed* order without an ORDER BY clause.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ames
Starting Member

7 Posts

Posted - 2011-08-30 : 13:17:00

Hi,
Thank you for your responses. What I am trying to do is as follows:

ItNo StID PID date rownum
800 451 8123290 6/2/2010 1
800 451 8123290 6/2/2010 2
800 451 8123290 6/2/2010 3
868 451 8262860 6/5/2010 4
868 451 8262860 6/5/2010 5
868 451 8262860 6/5/2010 6
1147 0 8/26/2011 7
1147 0 8/26/2011 8
1147 0 7/6/2010 9
1773 451 8262860 6/5/2010 10
1773 451 8262860 6/5/2010 11
1773 451 8262860 6/5/2010 12
1776 451 8262846 6/5/2010 13
1776 451 8262846 6/5/2010 14
1776 451 8262846 6/5/2010 15
1845 451 8262846 6/5/2010 16
1845 451 8262846 6/5/2010 17

If you look a the column ITNo, and STID where the PID is blank. I need a query tpo do a validation such as this: if for the values where stationid=0 or PID blank, look one above and below the value of the PID column, and if they are same, return the value which is same, if they are not same, return null. For this dataset the value one above and one below is 8262860 and it is same, so it shoudl return the 826286o. Please let me know if you have any questions.

Ames

ames
Starting Member

7 Posts

Posted - 2011-08-30 : 13:20:03

Here is the dataset, one more time, the previous one didnt show proprely.

ItNo StID PID date rownum
800 451 8123290 6/2/2010 1
800 451 8123290 6/2/2010 2
800 451 8123290 6/2/2010 3
868 451 8262860 6/5/2010 4
868 451 8262860 6/5/2010 5
868 451 8262860 6/5/2010 6
1147 0 ------- 8/26/2011 7
1147 0 ------- 8/26/2011 8
1147 0 ------- 7/6/2010 9
1773 451 8262860 6/5/2010 10
1773 451 8262860 6/5/2010 11
1773 451 8262860 6/5/2010 12
1776 451 8262846 6/5/2010 13
1776 451 8262846 6/5/2010 14
1776 451 8262846 6/5/2010 15
1845 451 8262846 6/5/2010 16
1845 451 8262846 6/5/2010 17

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 13:37:47

[code]
SELECT t.ItNo,
CASE WHEN t1.StID = t2.StID THEN COALESCE(NULLIF(t.StID,0),t1.StID) ELSE NULLIF(t.StID,0) END,
CASE WHEN t1.PID = t2.PID THEN COALESCE(NULLIF(t.PID,''),t1.PID) ELSE NULLIF(t.PID,'') END,
date,
rownum
FROM Table t
OUTER APPLY (SELECT TOP 1 PID,StID
FROM Table
WHERE rownum < t.rownum
ORDER BY rownum DESC) t1
OUTER APPLY (SELECT TOP 1 PID,StiD
FROM Table
WHERE rownum > t.rownum
ORDER BY rownum ASC) t2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ames
Starting Member

7 Posts

Posted - 2011-08-30 : 15:01:21

HI,
Thank you for the query but I generated rownum column using ROW_NUMBER() over(order by itno,pid,date desc). It is not recognizing the rownum column name:(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 00:12:46

thats ok put your current query with rownum in a cte and use it in above query
ie.


;With CTE(other columns...,rownum)
AS
(
SELECT other columns...,
your rownumber 
FROM....
..
)
SELECT t.ItNo,
CASE WHEN t1.StID = t2.StID THEN COALESCE(NULLIF(t.StID,0),t1.StID) ELSE NULLIF(t.StID,0) END,
CASE WHEN t1.PID = t2.PID THEN COALESCE(NULLIF(t.PID,''),t1.PID) ELSE NULLIF(t.PID,'') END,
date,
rownum
FROM CTE t
OUTER APPLY (SELECT TOP 1 PID,StID
             FROM CTE 
             WHERE rownum < t.rownum
             ORDER BY rownum DESC) t1
OUTER APPLY (SELECT TOP 1 PID,StiD
             FROM CTE 
             WHERE rownum > t.rownum
             ORDER BY rownum ASC) t2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ames
Starting Member

7 Posts

Posted - 2011-08-31 : 00:34:49

I will try this. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 00:44:27

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ames
Starting Member

7 Posts

Posted - 2011-08-31 : 01:15:14

Hi,
I generated this data set that I sent from the temptable. The rownum column didnt exist in temp input dataset. I added and hoping that I can work out some logic from it. If we assume rownum doesnt exist, then how can I return the result for; when PID is null look at the one up one down row and if they are equal return the PID which is equal to eachother else return null. I have just started to work with sql server and dont have too much experience with it.

Thanks again,
Ames

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 01:34:53

if rownum does not exist there should be some other unique valued column like date which you can use for getting sequence ordering

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources