Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

i want update the 2nd column based on first column: Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 3:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 14, 2014 10:14 PM
Points: 21, Visits: 145
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
Post #1433678
Posted Thursday, March 21, 2013 4:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 3,350, Visits: 3,649
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 question

There 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
Post #1433688
Posted Thursday, March 21, 2013 4:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 13,641, Visits: 11,516
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1433695
Posted Thursday, March 21, 2013 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
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 Moden's 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)
Post #1433792
Posted Thursday, March 21, 2013 7:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1433794
Posted Thursday, March 21, 2013 9:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:50 AM
Points: 29, Visits: 1,000
Logic ?

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

Post #1433883
Posted Thursday, March 21, 2013 10:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 13,328, Visits: 12,823
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 Moden's 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)
Post #1433894
Posted Thursday, March 21, 2013 11:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:50 AM
Points: 29, Visits: 1,000
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 ...

Post #1433929
Posted Thursday, March 21, 2013 11:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 3,350, Visits: 3,649
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 question

There 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
Post #1433939
Posted Thursday, March 21, 2013 12:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1433957
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse