Tough Consolidation of Rows

Question

Tough Consolidation of Rows

Valued Member

Points: 56

August 4, 2005 at 1:58 pm

Record	NumSet1	NumSet2	SetVal
1	201645112.0	201645311.0	A
2	201645312.0	201645567.0	A
3	201645568.0	201645823.0	A
4	201645824.0	201646070.0	B
5	201646071.0	201646075.0	A
6	201646076.0	201646079.0	B
7	201646080.0	201646111.0	A
8	201646112.0	201646143.0	A
9	201646144.0	201646190.0	W
10	201646191.0	201646200.0	A

Obviously the above html should be rendered to make it easier to view.

Here is my dilema. I have a table w/ 3 columns: NumSet1, NumSet2, and SetVal.

Notice that record1.NumSet2 has value of 201645311.0 and record2.NumSet1 has

a value of 201645312.0 - making it just 1 numeric bigger, and so forth

for the 3rd record.

Also notice they all have the same SetVal of: A

So in a new table those 3 records could be consolidated into 1

w/ the values of: NumSet1 being (NumSet1.record1); NumSet2 being

NumSet2.record3) and the SetVal being "A".

Again notice that there are differnt values of SetVal's

in b/t range values.

So I am wondering if it can be written to pull back a result

set of any given SetVal already consolidated?

Viewing 15 posts - 1 through 15 (of 33 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Should be pretty easy to do (not jixing it hopefully). Can you post the expected data you want from the query?

delatush Valued Member Points: 56 More actions · Answer 2

Yup Remi, of the above, a consolidation of the SetVal's equal to "A"

would be:

Record	NumSet1	NumSet2	SetVal
1	201645112.0	201645823.0	A
2	201646071.0	201646075.0	A
3	201646080.0	201646143.0	A
4	201646191.0	201646200.0	A

noeld SSC Guru Points: 96590 More actions · Answer 3

I haven't testted this but give it a shot:

select SetVal

, T1.Numbset1

, (select Min(Numbset2) from YourTable T3

where

T3.SetVal = T1.SetVal

and

T1.Numbset1 > T3.Numbset1

and

not exists (select * from YourTable T4 where T4.SetVal = T1.SetVal and T4.Numbset1 = T3.Numbset2 -1 )

) NumSet2

from YourTable T1

where not exists (select * from YourTable T2 where T1.SetVal = T2.SetVal and T1.Numbset1 = T2.Numset2 - 1)

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Told ya it wouldn't be hard... didn't even have to type it .

delatush Valued Member Points: 56 More actions · Answer 5

Ahhhh, almost. NumSet2 is returning the same number for every record.

noeld SSC Guru Points: 96590 More actions · Answer 6

AGAIN this is without SQL in front of me I think I made a little mistake

select SetVal

, T1.Numbset1

, (select Min(Numbset2) from YourTable T3

where

T3.SetVal = T1.SetVal

and

T1.Numbset1 > T3.Numbset1

and

not exists (select * from YourTable T4 where T4.SetVal = T1.SetVal and T3.Numbset1 = T4.Numbset2 -1 )

) NumSet2

from YourTable T1

where not exists (select * from YourTable T2 where T1.SetVal = T2.SetVal and T1.Numbset1 = T2.Numset2 - 1)

* Noel

Ray M SSC-Insane Points: 21093 More actions · Answer 7

Ray M

SSC-Insane

Points: 21093

August 4, 2005 at 3:32 pm

#579867

Nice.

noeld SSC Guru Points: 96590 More actions · Answer 8

This is tested (got SQL )

select SetVal

, T1.NumSet1

, (select Min(NumSet2) from YourTable T3

where

T3.SetVal = T1.SetVal

and

T3.NumSet2 > T1.NumSet1

and

not exists (select * from YourTable T4 where T4.SetVal = T3.SetVal and T4.NumSet1 = T3.NumSet2 + 1 )

) NumSet2

from YourTable T1

where not exists (select * from YourTable T2 where T1.SetVal = T2.SetVal and T1.NumSet1 = T2.Numset2 + 1)

order by 1,2

Cheers!

* Noel

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 9

This problem looks like the following problem:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=206795

The same type of solution can be applied. It's probably not better than Noel's suggestion, but it's an alternative

select t1.setval, t1.numset1, min(t2.numset2) from yourtable t1 inner join yourtable t2

on t1.setval = t2.setval

where

t1.numset1 < t2.numset2

and

not exists (select * from yourtable t3 where t3.setval = t1.setval and t3.numset2 = t1.numset1 - 1)

and

not exists (select * from yourtable t4 where t4.setval = t2.setval and t4.numset1 = t2.numset2 + 1)

group by t1.setval, t1.numset1

delatush Valued Member Points: 56 More actions · Answer 10

Thanks for the input everyone, though I might not be able to get this

going. Even isolating out the setVal's into its own table, the queries

seem to not finish - killing my servers recources.

The colums are indexed but there are 1032787 records still and

is creating real hang time.

Another possibility I am thinking of is maybe trying to just find the breaks

in the ranges, so then knowing that rec's ID, maybe using the min and max ID's

before it?

noeld SSC Guru Points: 96590 More actions · Answer 11

when you run this self joined queries I can expect that they are not particulary fast! but that is one thing and the other is that they KILL your server reources!! 1 million rows are not particulary too many.

Can you :

1. create an index on numSet1 and another on NumSet2 (NC)

2. Does your table has any Clustered index?

3. What kind on Memory or hardware are you dealing with ?

4. the fastest way for this is probably ... yes a "cursor" but not for that amount of records you have

5. do you have any othe processes contending for this table ? How long are you waiting for the query?

* Noel

delatush Valued Member Points: 56 More actions · Answer 12

No Clustered index's, one on NumSet1, and another on NumSet2.

Big server [a developement server] enough ram.

noeld SSC Guru Points: 96590 More actions · Answer 13

I would like you to create a clustered on (NumSet1,NumSet2) and make it unique

then try the query!

How long are you waiting?

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Longer than you I guess .

I place my bet to 30 minutes min.