andorid-db query to get last records or first of the group if the last record is duplicate of previous

Question

There is a table status that contains data as shown below:

Id    Status     TimeStamp
-------------------------
1    StatusC     1234561
2    StatusC     1234562
3    StatusB     1234563
4    StatusA     1234564
5    StatusA     1234565
6    StatusA     1234566

What query will return the following result?

Id    Status     TimeStamp
-------------------------
4    StatusA     1234564

Another example :

Id    Status     TimeStamp
-------------------------
1    StatusC     1234561
2    StatusC     1234562
3    StatusB     1234563
4    StatusA     1234564
5    StatusA     1234565
6    StatusB     1234566

Result :

Id    Status     TimeStamp
-------------------------
6    StatusB     1234566

So i want the last status record if it is different from previous one or if the previous of last is same status then the first of that group. I tried couple thing but i am not getting the required result. Is there any efficient ways to achieve the result?

is the id column sequential with no gaps? – Vamsi Prabhala Dec 22 '16 at 18:54 — Vamsi Prabhala, Dec 22 '16 at 18:54

Charles Bretana · Accepted Answer · 2016-12-22T19:13:10.653

You want the earliest (lowest timestamp) record (row) with the same status as the latest (highest timestamp) row, but that does not have another row after it with a different status.

try this:

 Select *
 from table p
 where p.Timestamp = 
     (Select min(TimeStamp) from table x
      where status = 
           (Select Status from table 
            Where timestamp = 
               (Select Max(timestamp) From table))
          and not exists 
                 (Select * from table
                  Where timestamp > x.timeStamp
                      and status != p.Status))

andorid-db query to get last records or first of the group if the last record is duplicate of previous

1 Answers1