Need Help - SQL Select Statement

Question

Need Help - SQL Select Statement

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

rxm119528 (8/27/2014)
keshabsingh (8/27/2014)
Hints: you need to join the same table multiple times.
with CTE(rn,steps)
As
(
select row_number() over ( order by steps),steps from #table1)
select t1.steps,t2.steps from CTE t1
left outer join CTE t2
on t1.rn = t2.rn-1
where t2.steps is not null

No left outer join required. Your where clause forces the outer join to behave like an inner join.

if object_id('tempdb..#table1') is not null drop table #table1;

create table #table1(steps int);

insert into #table1(steps)values(1);

insert into #table1(steps)values(3);

insert into #table1(steps)values(4);

insert into #table1(steps)values(5);

with BaseData as (

select

steps,

rn = row_number() over (order by steps)

from

#table1

)

select

bd1.steps,

bd2.steps

from

BaseData bd1

inner join BaseData bd2

on (bd1.rn = bd2.rn - 1);

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

keshabsingh (8/27/2014)
Thanks Eirikur Eiriksson!.
Your attempt is not bad. The approaches other users presented are more robust and accurate. Thanks anyway.

Measuring and comparing the relative properties of different queries - even accuracy (which could depend upon the sample data) - is not straightforward and usually takes a fair amount of code and time, and a good deal of experience. Most of the regulars around here, including the folks who have chipped in on this thread with their contributions, know exactly how much experience and effort is required. Are you willing to share your methods with us?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

keshabsingh (8/27/2014)
Thanks Eirikur Eiriksson!.
Your attempt is not bad. 😛The approaches other users presented are more robust and accurate. Thanks anyway.

I guess that now it's your turn to make an effort.

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Eirikur Eiriksson (8/27/2014)
keshabsingh (8/27/2014)
Thanks Eirikur Eiriksson!.
Your attempt is not bad. 😛The approaches other users presented are more robust and accurate. Thanks anyway.
I guess that now it's your turn to make an effort.
😎

The OP should have posted their attempt first before anyone else jumped in with potential code for the problem.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

Lynn Pettis (8/27/2014)
Eirikur Eiriksson (8/27/2014)
keshabsingh (8/27/2014)
Thanks Eirikur Eiriksson!.
Your attempt is not bad. 😛The approaches other users presented are more robust and accurate. Thanks anyway.
I guess that now it's your turn to make an effort.
😎
The OP should have posted their attempt first before anyone else jumped in with potential code for the problem.

I agree, as guilty as I am, couldn't resist the inaccuracy in the initial post. Guess I've been tainted by the Brits and they'r ironic sarcastic humour. We should maybe turn this into a contorted code competition?:w00t:

😎

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

Again, quick question, what version and edition of sql server (select @@version) are you on? You do know that it is kind of offensive not to answer questions when someone is trying to help!

😎

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 7

Eirikur Eiriksson (8/27/2014)
keshabsingh (8/27/2014)
This is not homework session. Your approach is very limited with hard coding the values. What if data is like this?
Then, that in itself is a change to the initial requirements, which mind you are more limited than my hard coded values.;-) The sum of the values indicate a certain sequence could be another hint there who knows?
It all boils down to the basics, if you are looking for a proper answer, ask a proper question!
😎

I thought the OP asked a pretty good question not to mention posting readily consumable data and the expected result. It's our "job" to remember that it's just test data and will vary in real life... just like we sometimes explain to other OPs. 🙂

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Jeff Moden (8/27/2014)
Eirikur Eiriksson (8/27/2014)
keshabsingh (8/27/2014)
This is not homework session. Your approach is very limited with hard coding the values. What if data is like this?
Then, that in itself is a change to the initial requirements, which mind you are more limited than my hard coded values.;-) The sum of the values indicate a certain sequence could be another hint there who knows?
It all boils down to the basics, if you are looking for a proper answer, ask a proper question!
😎
I thought the OP asked a pretty good question not to mention posting readily consumable data and the expected result. It's our "job" to remember that it's just test data and will vary in real life... just like we sometimes explain to other OPs. 🙂

Yes, and he even gave a hint to what needed to be done, so where was his attempt at solving the problem? Nonexistent. Sure looked like home work so I asked for his code. Then everyone jumped in and gave possible solutions.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 9

My apologies if I was out of line earlier, somehow I get kind of agitated when requirements focus more on HOW than WHAT, especially when it's incorrect.

The problem itself is elementary, sequential self-join or Lead/Lag on 2012 and later, hence the question about the Version/Edition.

😎

USE tempdb;

GO

create table #table1(steps int)

insert into #table1(steps)values(1)

insert into #table1(steps)values(2)

insert into #table1(steps)values(3)

insert into #table1(steps)values(4)

insert into #table1(steps)values(5)

insert into #table1(steps)values(6)

insert into #table1(steps)values(7)

insert into #table1(steps)values(9)

insert into #table1(steps)values(10)

insert into #table1(steps)values(17)

insert into #table1(steps)values(31)

;WITH BASE_DATA AS

(

select

ROW_NUMBER() OVER (ORDER BY T.steps) AS T_RID

,T.steps

from #table1 T

)

SELECT

B_FROM.steps AS stepFrom

,B_TO.steps AS stepTo

FROM BASE_DATA B_FROM

INNER JOIN BASE_DATA B_TO

ON B_FROM.T_RID = B_TO.T_RID - 1

DROP TABLE #table1;

/* Window Function 2012 */

SELECT

X.stepFrom

,X.stepTo

FROM

(

SELECT

steps AS stepFrom

,LEAD(steps,1) OVER (ORDER BY steps) AS stepTo

FROM #table1

) AS X

WHERE X.stepTo IS NOT NULL;

Results

stepFrom stepTo

----------- -----------

1 2

2 3

3 4

4 5

5 6

6 7

7 9

9 10

10 17

17 31

Edit: forgot the Lead version

David Burrows SSC Guru Points: 65142 More actions · Answer 10

Eirikur Eiriksson (8/27/2014)

... Guess I've been tainted by the Brits and they'r ironic sarcastic humour ...

In that case

SELECT a.steps,MIN(b.steps)

FROM #table1 a

CROSS JOIN #table1 b

WHERE b.steps > a.steps

GROUP BY a.steps

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 11

David Burrows (8/28/2014)

Eirikur Eiriksson (8/27/2014)
... Guess I've been tainted by the Brits and they'r ironic sarcastic humour ...
In that case
SELECT a.steps,MIN(b.steps)
FROM #table1 a
CROSS JOIN #table1 b
WHERE b.steps > a.steps
GROUP BY a.steps

🙂 Triangular humor.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Jeff Moden (8/28/2014)

David Burrows (8/28/2014)
Eirikur Eiriksson (8/27/2014)
... Guess I've been tainted by the Brits and they'r ironic sarcastic humour ...
In that case
SELECT a.steps,MIN(b.steps)
FROM #table1 a
CROSS JOIN #table1 b
WHERE b.steps > a.steps
GROUP BY a.steps
🙂 Triangular humor.

How obtuse!!! 😛

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/