CODEWITHSUNDEEP
sqlpostgresql
anneb
anneb

Reputation: 5768

SQL: Get min and max for groups in ordered table

I have a table with drilling samples. The table has one row for every unit of depth listing the prevalent material type at that depth. From this table I want to get the top (maximum) and bottom (minimum) depth of each layer type. How can this be done in SQL? If I use "group by" the depth ordering is lost, if I use "order by" I cannot use 'min(depth)' and 'max(depth)'?

Example input table 

|sampleid | layertype | depth|
+----------------------------+
| 1       | sand      |  0  | 
| 1       | sand      | -1  | 
| 1       | peat      | -2  | 
| 1       | sand      | -3  |
| 1       | sand      | -4  |
| 1       | sand      | -5  |
| 1       | clay      | -6  |
| 1       | clay      | -7  |
| 1       | clay      | -8  |
| 1       | sand      | -9  |
| 1       | sand      | -10 |
| 1       | sand      | -11 |
+----------------------------+

Desired output table:

|sampleid | layertype | top | bottom |
+------------------------------------+
| 1       | sand      |  0  | -1     |
| 1       | peat      | -2  | -2     | 
| 1       | sand      | -3  | -5     |
| 1       | clay      | -6  | -8     |
| 1       | sand      | -9  | -11    |
+------------------------------------+

This kind of question is likely asked many times on StackOverflow, but I was unable to find an answer or duplicate, maybe I am not using the correct search words?

Upvotes: 0

Views: 247

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This is a gaps-and-islands problem. I would recommend the difference of row numbers:

select sample_id, layer_type, 
       max(depth) as top, min(depth) as bottom
from  (select t.*,
              row_number() over (partition by sample_id order by depth) as seqnum,
              row_number() over (partition by sample_id, layer_type order by depth) as seqnum_lt
       from sample_table t
      ) t
group by sample_id, layer_type, (seqnum - seqnum_lt)
order by sample_id, min(depth) desc;

Upvotes: 4

Related Questions