[Show all top banners]

NepaliBhai
Replies to this thread:

More by NepaliBhai
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 SQL Select Statement
[VIEWED 4716 TIMES]
SAVE! for ease of future access.
Posted on 08-27-14 9:24 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Run this script
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)

select steps from #table1
drop table #table1

You will get this
steps
1
3
4
5

I want this way.
stepFrom    stepTo
1                3
3                4
4                5
Hints: you need to join the same table multiple times.
Last edited: 27-Aug-14 09:30 AM

 
Posted on 08-27-14 11:35 AM     [Snapshot: 131]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Here you go NepaliBhai......................


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)

select distinct x.stepFrom, Y.stepTo from
(select rank()over(order by a.steps )as rank,a.steps as stepFrom from (select top 3 * from #table1 order by 1 asc) a
left outer join #table1 b
on a.steps =b.steps) x
inner join
(select rank()over(order by a.steps )as rank,a.steps as stepTo from (select top 3 * from #table1 order by 1 desc) a
left join #table1 b
on a.steps =b.steps) y
on x.rank =y.rank

drop table #table1
 
Posted on 08-27-14 11:48 AM     [Snapshot: 142]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

SQL_PRO, i don't think you should be using rank function. Can you try using only ANSI SQL :)?
Btw, if you are using sql 2012 this thing can be done with ease
Select * from
(select steps as StepsFrom,lead(steps) over (order by steps) as StepsTo from #table1 ) t1
where StepsTo is not null

But ANSI SQL would be more interesting :)
 
Posted on 08-27-14 1:08 PM     [Snapshot: 209]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 


SQL_PRO Dude,
It doesn't work that way. You can't hard code like top 3. It should be dynamic.

What if the data is like this.
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(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)

So the solution is like this

go
WITH TEMP_CTE AS (
SELECT steps, ROW_NUMBER() OVER(ORDER BY steps) AS ROW_NUM FROM #table1
)
SELECT t1.steps, t2.steps FROM TEMP_CTE t1, TEMP_CTE t2
WHERE t1.ROW_NUM < (SELECT MAX(ROW_NUM) FROM TEMP_CTE)
AND (t2.ROW_NUM - 1) = t1.ROW_NUM

 
Posted on 08-27-14 2:17 PM     [Snapshot: 259]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Here is another puzzle.
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)
Now generate random rows for these rows.
e.g
1
3,
5,
4
or
5,
3,
1,
4

Remember, you need to produce random rows every time you run it.

 
Posted on 08-27-14 2:41 PM     [Snapshot: 280]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 


I like your idea. It will be fun to work on. I will try to come up with syntax.

 
Posted on 08-28-14 7:43 AM     [Snapshot: 496]     Reply [Subscribe]
Login in to Rate this Post:     0       ?    
 

Here it is.

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)

SELECT cast(steps as varchar) + ',' FROM #table1 ORDER BY NEWID()

drop table #table1
 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 30 days
Recommended Popular Threads Controvertial Threads
What are your first memories of when Nepal Television Began?
मन भित्र को पत्रै पत्र!
काेराेना सङ्क्रमणबाट बच्न Immunity बढाउन के के खाने ?How to increase immunity against COVID - 19?
TPS Work Permit/How long your took?
चितवनको होस्टलमा १३ वर्षीया शालिन पोखरेल झुण्डिएको अवस्था - बलात्कार पछि हत्याको शंका - होस्टेलहरु असुरक्षित
Nepali doctors future black or white usa ?
Another Song Playing In My Mind
nrn citizenship
TPS Renewal Reregistration
हेर अमेरिकामा नेपालीहरुको बेज्जत
WHAT DO YOU GUYS THINK ABOUT THIS?
Send Parcels from USA to Nepal & Worldwide.
Why is every youths leaving Nepal? Why are not youths entering politics and serving the country, starting business etc?
Is money sent to support family tax deductible?
Nearly one-third of Americans support autocracy, poll finds
Alert: Turbo Cancers: A Rising Global Threat
महँगो अण्डाको पिकल्प : कुखुरा र खोर भाडामा लिने
Informatica consultancy share
Travelling on TPS advance travel document to different country...
Are you ready to know the truth?
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters