Need Low and High numbers for each group

[bmacbmac]

Hi, I have mytable created with the following fields/data:

LANDID RECORDID SUB BLOCK LOT
154 140 TESTSUB 1 1
154 140 TESTSUB 1 2
155 140 TESTSUB 2 2
155 140 TESTSUB 2 3
155 140 TESTSUB 2 4
156 140 TESTSUB 3 1
156 140 TESTSUB 3 2
156 140 TESTSUB 3 3
156 140 TESTSUB 3 13
156 140 TESTSUB 3 14
156 140 TESTSUB 3 15
156 140 TESTSUB 3 20
157 140 TESTSUB 4 1
157 140 TESTSUB 4 2
157 140 TESTSUB 4 3
157 140 TESTSUB 4 4
157 140 TESTSUB 4 5
157 140 TESTSUB 4 10
158 140 TESTSUB 5 1

I would like to group each landid and/or block to find the high/low lots for each series. For example,

Landid 154: Block 1, LowLot 2, HighLot 3
Landid 155: Block 2, LowLot 2, Highlot 4
Landid 156: Block 3, Lowlot 1, Highlot 3
Landid 156: Block 3, Lowlot 13, Highlot 15
Landid 156: Block 3, Lowlot 20, Highlot 20
Landid 157: Block 4, Lowlot 1, Highlot 5
Landid 157: Block 4, Lowlot 10, Highlot 10
Landid 158: Block 5, Lowlot 1, Highlot 1

Notice how block 3 and 4 have multiple groups of high/low lots?

I can find the low/high each block, but not sure how to locate the gap in sequence and break it down by each series. When i do it my low lot for block 3 is 1 and the high lot is 20. But this is not what I need.

Anyone have any ideas for me?

Thanks!

Brian

 

[r937]

LANDID RECORDID SUB BLOCK LOT
154 140 TESTSUB 1 [red]1[/red]
154 140 TESTSUB 1 [red]2[/red]

Landid 154: Block 1, LowLot [red]2[/red], HighLot [red]3[/red]



you lost me right there




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bmacbmac]

Sorry about that. Type-O on my part.

Should be Lowlot 1, Highlot 2

 

[markros]

The problem is known as identifying islands of data. Take a look at this blog post by Plamen Ratchev as how to solve such queries:
Refactoring Ranges

PluralSight Learning Library