sqlite - get oldest row that does not meet certain criteria as long as there are not too many rows older

sharkyenergy 0
Viewed 24

what I need is somwhat difficult for me to explain, so i try to approach it step by step..

I do have a sample database with a few rows. I would need a query that extracts the oldest row (smallest id) that has the score > 600 and has the "flag" column at 0.

so far, i have solved it with this query: select * from test where score >= 600 and flag = 0 order by id asc limit 1

in the sample sqlFiddle it returns the row with id 10. so 1-9 do not meet the requirement. what I actually would need, is row 1 tough, because I want to skip only the first 5 entries that do not meet the above requirement. so, there are two cases:

a) there are less than 5 rows with id < than the returned row: keep the row.

b) there are equal or more than 5 rows with id < than the returned row: discard the result and take the overall oldest row, regardless if it meets the above criteria or not.

how can this be done?

Thank you!

http://sqlfiddle.com/#!7/476e1a/2

sqlite
1 Answers

I solved it this way:

select *,
       CASE WHEN score >=600 and flag = 0
       THEN 1
       ELSE 0
       END AS scoreBinary
       from (select * from test order by id asc limit 6) as A order by scoreBinary desc, id asc limit 1

If there is a more elegant/better solution, please post it.

sharkyenergy0
Related