May 29, 2008 at 8:41 am
col1 col2 col3
1IN NULL
2FOR 1
3Small 4
4World 1
5Hj 1
6NJ 1
7Welcome 1
8So 4
9We 1
10Are 1
11Resp 3
12OIJIOJPOJPJO7
13OIJOIJ 1
14LKJ 2
15IJ 1
Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1
for eg., my 1st set would be
1IN NULL
2FOR1
2nd set would look something like this
3Small 4
4World1
5Hj 1
6NJ 1
7Welcome1
3rd set
8So4
9We1
10Are1
4th Set
12OIJIOJPOJPJO7
13OIJOIJ1
and the last set
14LKJ2
15IJ1
Any help is appreciated!!
May 29, 2008 at 9:45 am
Hi there,
Do you want this in multiple result sets?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 29, 2008 at 9:48 am
yes at the time of calculation, I will build a string for for those sets
right now I am doing it very ugly way using loops, so I was just wondering if there is a better way to do it
Thanks
May 29, 2008 at 9:52 am
HI again,
To be honest I would be very interested to see if you could return multiple result sets without a loop or a recursive call to function/Proc or multiple select statements.
So I'm gonna say that there isn't another way, but if someone else could clear this up it would be great.
Sorry I couldn't be more help
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 29, 2008 at 10:07 am
Thanks for your help Cris, if you had to return multiple recordset in that case, then can you do that in one shot?
because I am doing that inside the loop and processing each individual item
so the complexity is pretty doubled.
May 29, 2008 at 10:11 am
HI,
Let me have a look at it , I'm pretty sure you don't need to access every row one by one to get the multiple sets.
I'll get back to you on that.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 29, 2008 at 10:17 am
If you plan on concatenating - you don't need multiple datasets. You just need a way to know where the sequences start and end. This will help you with that:
create table #MyTable(col1 int, col2 varchar(20),col3 int)
insert #MyTable
select 1,'IN', NULL union all
select 2,'FOR',1 union all
select 3,'Small',4 union all
select 4,'World', 1 union all
select 5,'Hj', 1 union all
select 6,'NJ', 1 union all
select 7,'Welcome', 1 union all
select 8,'So',4 union all
select 9,'We', 1 union all
select 10,'Are', 1 union all
select 11,'Resp', 3 union all
select 12,'OIJIOJPOJPJO',7 union all
select 13,'OIJOIJ', 1 union all
select 14,'LKJ', 2 union all
select 15,'IJ', 1
;With bobCTE as
(select col1, Row_number() over (order by col1) seq
from #MyTable
where col3<>1 or col3 is null),
rangeCTE as
(select b1.seq Sequence,
b1.col1 as startrange,
b2.col1 as endrange
from BobCTE b1
left outer join BobCTE b2 on b1.seq=b2.seq-1)
select Sequence, col1, col2, col3
from #MyTable
Join RangeCTE on col1>=startrange and col1<isnull(endrange,col1+1)
Drop table #MyTable
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2008 at 10:27 am
VERY nice Matt,
I like that query 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 29, 2008 at 10:31 am
Matt, YOU ROCK!!
May 29, 2008 at 10:33 am
Christopher Stobbs (5/29/2008)
VERY nice Matt,I like that query 🙂
Thanks! Although if the data gets big - I'd think about going with Jeff's running totals solution instead (which incidentally could be used to wire up the concatenation right off the bat with no intermediary output).
http://www.sqlservercentral.com/articles/Test+Data/61572/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2008 at 11:03 am
Matt's solution has a problem. If the first row has col3=1, it will be omitted. The issue stems from finding the break points so if the first item is a break point, it's omitted. I had the same problem with my first cut omitting the last set. This version uses a local table so I can append an additional break to include the final set.
create table #test (col1 int, col2 varchar(25), col3 int)
insert into #test values (1,'IN',NULL) -- change NULL to 1 test first set
insert into #test values (2,'FOR',1)
insert into #test values (3,'Small',4)
insert into #test values (4,'World',1)
insert into #test values (5,'Hj',1)
insert into #test values (6,'NJ',1)
insert into #test values (7,'Welcome',1)
insert into #test values (8,'So',4)
insert into #test values (9,'We',1)
insert into #test values (10,'Are',1)
insert into #test values (11,'Resp',3)
insert into #test values (12,'OIJIOJPOJPJO',7)
insert into #test values (13,'OIJOIJ',1)
insert into #test values (14,'LKJ',2)
insert into #test values (15,'IJ',1)
declare @groups table (col1 int, seq int identity(1,1))
insert into @groups
select col1
from #test where col3 != 1
insert into @groups
select max(col1)+1 from #test
select X.setNum, T.*
from #test as T join
( select isnull(A.col1,0) as firstCol, B.col1 - 1 as lastCol,
row_number() over (order by B.col1) as setNum
from @groups as B left outer join @groups as A
on B.seq = A.seq + 1 ) as X
on T.col1 between X.firstCol and X.lastCol
May 29, 2008 at 11:20 am
Good catch Antonio. One small change will help with that. Still looking for breaks, but forcing one at the beginning:
declare @start int
set @start=min(col1) from #myTable;
;With bobCTE as
(select col1, Row_number() over (order by col1) seq
from #MyTable
where col3<>1 or col3 is null
Or Col1=@start), --<<-- change is here.
rangeCTE as
(select b1.seq Sequence,
b1.col1 as startrange,
b2.col1 as endrange
from BobCTE b1
left outer join BobCTE b2 on b1.seq=b2.seq-1)
select Sequence, col1, col2, col3
from #MyTable
Join RangeCTE on col1>=startrange and col1<isnull(endrange,col1+1)
Drop table #MyTable
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2008 at 11:29 am
Thats true, but in my case the top row will always have NULL value because that col is a difference with previous row and if no previous row exists then it is a NULL because don't want the -ive and 0's.
But that was a nice catch.
Thanks all for your help!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply