How to select all records where a field is of a certain value until a record shows up that has a different value?

Question

Let's say that we have a table with COLUMN1 and COLUMN 2. Here's a sample of the records:

COLUMN 1 | COLUMN 2
124 | 12
124 | 11
124 | 10
124 | 9
26  | 8
65  | 7
65  | 6
65  | 5
65  | 4
23  | 3
124 | 2
124 | 1
124 | 0

There is absolutely no pattern to this, but what I'd like to do is get:

COUNT(*) | COLUMN 1 | Smallest Column 2
4 | 124 | 9
1 | 26  | 8
4 | 65  | 4
1 | 23  | 3
3 | 124 | 0

So far, I've been doing this with PHP, but I'd like to find a way to do this in MySQL, as I'm sure it'd be a lot more efficient. The problem is, I can't even think of where to start with this. A regular GROUP BY COLUMN 1 wouldn't work because I want two results for 124, since it appears in two different instances. I've been fiddling around for hours and looking into the documentation and Google, but I haven't been able to find anything yet, and I was wondering if any of you would be able to point me in the right direction. Is this even possible with MySQL?

Is there anything that orders them like that? Without some explicit ordering the table is, more or less, an unordered set; runs of COLUMN1 aren't guaranteed to be the same from one query to the next unless you force the matter using an ORDER BY. — mu is too short, Feb 01 '12 at 06:08
If I read the question correctly, the order is column 2 descending. — nolt2232, Feb 01 '12 at 06:49

score 2 · Accepted Answer · answered Feb 01 '12 at 15:40

Well, it took a bit of fiddling, but here it is!

This assumes you have an id column in your table that you order by to get a consistent ordering (if you don't have an id column, order by timestamp or whatever in the inner query).

set @prev := '', @low := 0, @cnt := 0, @grp :=0;
select cnt, column1, low
from (
    select
        column2, 
        @low := if(@prev = column1, least(column2, @low), column2) low,
        @cnt := if(@prev = column1, @cnt + 1, 1) cnt,
        @grp := if(@prev = column1, @grp, @grp + 1) grp,
        @prev := column1 column1
    from (select column1, column2 from so9091342 order by id) x
    order by grp, cnt desc) y
group by grp;

Here's the sql needed to set up a table for testing:

create table so9091342 (id int primary key auto_increment, column1 int, column2 int);
insert into so9091342 (column1, column2) values (124,12),(124,11),(124,10),(124,9),(26,8),(65,7),(65,6),(65,5),(65,4),(23,3),(124,2),(124,1),(124,0);

Output of above query:

+------+---------+------+
| cnt  | column1 | low  |
+------+---------+------+
|    4 |     124 |    9 |
|    1 |      26 |    8 |
|    4 |      65 |    4 |
|    1 |      23 |    3 |
|    3 |     124 |    0 |
+------+---------+------+

p.s. I named the table so9091342 because this is SO question ID #9091342.

That is awesome. :D Thank you so much, that is perfect. And I've just learned how to make the most of variables. — Bruno De Barros, Feb 02 '12 at 15:39

score 1 · Answer 2 · edited May 23 '17 at 12:11

Interesting question. I know Oracle much better than MySQL so I was able to get it working in Oracle. Might be a better way but this is what I came up with.

select count(col1) as cnt, col1, min(col2) as smallestCol2
from (
  select col1, col2, col2-rnk as rnk
  from 
  (
    select col1, col2, RANK() OVER (PARTITION by col1 order by col2 asc) as rnk
    from tmp_tbl
  )
)
group by col1,rnk
order by min(col2) desc

I'm not quite sure how rank and partition work in MySQL but this might be helpful:

Rank function in MySQL

EDIT: To clarify what is going on in my query:

The inner query assigns a unique counter (RNK) to each value in column 1. The result of the most inner query is:

COL1 COL2   RNK
23   3      1
26   8      1
65   4      1
65   5      2
65   6      3
65   7      4
124  0      1
124  1      2
124  2      3
124  9      4
124  10     5
124  11     6
124  12     7

By subtracting the rank from column 2, you can get a unique value for each grouping of column 1 values. The result of the second nested query is:

COL1 COL2  RNK
23   3     2
26   8     7
65   4     3
65   5     3
65   6     3
65   7     3
124  0     -1
124  1     -1
124  2     -1
124  9     5
124  10    5
124  11    5
124  12    5

Then you can group on column 1 and that unique value. The final result:

CNT COL1 SMALLESTCOL2
4   124  9
1   26   8
4   65   4
1   23   3
3   124  0

You, sir, are a genius. I'm quite disappointed that MySQL doesn't have that ranking functionality, but with the use of variables, I'm getting closer to it. I haven't managed to solve it yet, but I'm on it. I'll post the answer here when I'm done. Thank you! :) — Bruno De Barros, Feb 01 '12 at 10:58

How to select all records where a field is of a certain value until a record shows up that has a different value?

2 Answers2