SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


i want update the 2nd column based on first column:


i want update the 2nd column based on first column:

Author
Message
DBA.A
DBA.A
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 388
hear i have id column based on that getting one more column as mid and data as belo:

id
100
200
300
400
500

Expected output:

id Mid
100 NULL
200 100
300 200
400 300
500 400
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5790 Visits: 4736
What logic are you using to determine the value of the second column?
I can guess of a few - my guesses can be wrong though.

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34219 Visits: 13270
No chance you are using SQL Server 2012? Because this would be easy with the new lag/lead functions.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Sean Lange
Sean Lange
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33140 Visits: 17669
Agreed that if using 2012 there are easier options. Also would be better if we knew what your requirements are. I took a shot in the dark.


;with Data(ID)
as
(
select 100 union all
select 200 union all
select 300 union all
select 400 union all
select 500
)

select *
from Data d
outer apply
(
select top 1 ID from Data d2 where d2.ID < d.ID order by ID desc
) x



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114681 Visits: 41394
This is a classic problem and the classic solution, which runs on all versions of SQL Server, turns out to be quite fast.

--===== Create the test data.
-- This is NOT a part of the solution.
-- We're just building the test data table
-- to look as expected.
SELECT ID = Number
INTO #YourTable
FROM master.dbo.spt_values
WHERE Type = 'P'
AND Number %100 = 0
;
--===== Add the expected clustered index.
-- This is also NOT a part of the solution.
-- We're just building the test data table
-- to look as expected.
ALTER TABLE #YourTable
ADD PRIMARY KEY CLUSTERED (ID)
;
--===== This is the classic solution that will work on all versions of
-- SQL Server and is very fast in the presence of the correct index.
SELECT ID, MID = (SELECT TOP 1 ID FROM #YourTable t2 WHERE t2.ID < t1.ID ORDER BY t1.ID DESC)
FROM #YourTable t1
;



{EDIT} If you compare the code above to Sean's good code, it also demonstrates that APPLY isn't much more than a sophisticated correlated sub-query.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
carlosaamaral
carlosaamaral
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 1049
Logic ?

 
MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)


Sean Lange
Sean Lange
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33140 Visits: 17669
carlosaamaral (3/21/2013)
Logic ?

 
MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)



That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
carlosaamaral
carlosaamaral
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 1049
Sean Lange (3/21/2013)
carlosaamaral (3/21/2013)
Logic ?

 
MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)



That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.


ok ... but sometimes the simple, we do not see ... Without data, it really is very, very complicated ...
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5790 Visits: 4736
carlosaamaral (3/21/2013)
Sean Lange (3/21/2013)
carlosaamaral (3/21/2013)
Logic ?

 
MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)



That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.


ok ... but sometimes the simple, we do not see ... Without data, it really is very, very complicated ...


Exactly - which is why most of my answers to questions don't involved code. We don't see the logic (business or otherwise) behind the question - only the desire to see the result.

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114681 Visits: 41394
carlosaamaral (3/21/2013)
Sean Lange (3/21/2013)
carlosaamaral (3/21/2013)
Logic ?

 
MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)



That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.


ok ... but sometimes the simple, we do not see ... Without data, it really is very, very complicated ...



Absolutely agreed. It's just that some of us have been doing this so long that we've learned to anticipate various data "possibilites" and to bulletproof the code a bit.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search