CODEWITHSUNDEEP
mysqlsql
Webfarmer
Webfarmer

Reputation: 263

SQL Query to return count of rows until certain criteria is met

I've got the following dataset ordered by a specific column:

ratio
-----
1
1
1
0.8333
1
1.6667
3.3333
1

And I want to count the rows where ratio equals 1, but only until I reach a row where ratio is not 1. For the above dataset my expected result would be 3 (the first three rows).

Of course I could do this in the code, but I just wondered whether there's an SQL solution to this.

Upvotes: 0

Views: 4438

Answers (4)

Mehdi Seyedain
Mehdi Seyedain

Reputation: 1

Gordon's solution does not work when table consists only of 1s. Maybe this solution will work for you

SELECT  COUNT(*) AS CNT
  FROM
(
  SELECT  SUM(CASE WHEN ratio = 1 THEN 1 ELSE 0 END) OVER(ORDER BY SortColumn DESC) AS NotOneCount
    FROM YourTable
) t
WHERE t.NotOneCount = 0;

Upvotes: 0

Yurii K
Yurii K

Reputation: 162

You can also try to use update 

declare @cnt int, @flag int
select @cnt = 0, @flag = null
update #t set
    @cnt = @cnt + case when @flag is null then ratio else 0 end,  
    @flag = case when @flag is null and ratio != 1 then 1 else @flag end
select @cnt

I am using t-sql syntax, but you may find something similar in

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You say that the data is "ordered by a specific column". If so, you can simply do:

select count(*)
from table t
where specificcolumn < (select min(t2.specificcolumn)
                        from table t2
                        where t2.ratio <> 1)

Depending on the ordering, the < may need to be >.

Note: this assumes that the specific column has unique values. If the values are not unique, then you need multiple columns for a unique key.

Upvotes: 2

Ananth
Ananth

Reputation: 4397

If you have another primary key column in the table:

SELECT COUNT(`id`)
FROM `table`
WHERE `ratio` = 1
AND `id` < (SELECT `id` FROM `table` WHERE `ratio` != 1 ORDER BY `id` ASC LIMIT 0, 1)

Upvotes: 1

Related Questions