SQL - Find Continuous Records
752564Feb 9 2010 — edited Feb 10 2010I am looking for some SQL advice on finding continuous records in a table. The table in questions looks something like this:
ID LITH DEPTH
1-1 SAND 150
1-1 COAL 200
1-1 SAND 250
1-1 COAL 300
2-2 SAND 75
2-2 COAL 100
2-2 COAL 150
2-2 COAL 200
2-2 COAL 250
2-2 SAND 300
2-2 COAL 400
2-2 COAL 450
I am trying to locate the records marked in bold above and count the number of times they occur. In the example above I would hope to return:
id count
1-1 null
2-2 4
2-2 2
I know this is a problem that can be solved outside of the database, with excel for example. However, I would really appreciate any advice on how to solve this problem with SQL.