CODEWITHSUNDEEP
sqlsqlite
Johann
Johann

Reputation: 29877

SQL to solve this

I have the following data in a table I'll call TableA:

ID  Status     Date
5     0        1000
20    0         900
10    1         800
30    1         700
4     1         600
8     0         500
22    1         400
1     1         300
3     0         200

The records are sorted by Date descendingly. I want to get only those records where Status is equal to 1 BUT only up to the first record where the Status is no longer 1. So in the sample data, records with ID: 10,30,4 would be selected but but 22 and 1 would not be because ID 8 appears and separates the sets. Preferrably the SQL should run in Sqlite. The result for this sample data should return:

ID  Status     Date
10     1       800
30     1       700
4      1       600

EDIT I replaced the ID values with random values and changed the date from TEXT to Integer.

Upvotes: 1

Views: 101

Answers (3)

ErikE
ErikE

Reputation: 50251

Here you go:

SELECT *
FROM
   TableA A
   INNER JOIN (
      SELECT *
      FROM TableA S
      WHERE S.Status = 1
      ORDER BY S.Date DESC
      LIMIT 1
   ) S ON A.Date <= S.Date
WHERE
   A.Status = 1
   AND A.Date > (
      SELECT E.Date
      FROM TableA E
      WHERE
         E.Status = 0
         AND S.Date > E.Date
      ORDER BY Date DESC
      LIMIT 1
   )
;

See a Live Demo at SQL Fiddle

This should be pretty efficient because of the LIMIT clauses. If there are many rows in the table it theoretically won't be scanning them all--but big disclaimer: I don't work with sqlite much at all.

Upvotes: 0

Vic
Vic

Reputation: 1

this is not tested, but will give an idea.

It's for MSSQL and uses subqueries; I dont know if it works for sqlite.

select RowNumber() r, *
from (select * from TableA where status = 1), (select top 1 id from TableA where status = 1) diff
where id - r = diff - 1

Upvotes: -1

Patashu
Patashu

Reputation: 21783

I suggest

select * from tableA a1 where a1.status = 1 and not exists
(select 1 from tableA a2 where a2.status = 0 and a2.date > a1.date and a2.date <
(select max(date) from tableA a3 where a3.status = 1
)
)

Doubly nested subquery. Select rows where the status is 1 that have no rows before them with (status is 0 and that are after the earliest row where status is 1).

No idea how efficient this is.

Upvotes: 2

Related Questions