Dias Dias - 1 year ago 56
SQL Question

Compare the same column in same table

I need some help solving this problem.
I have the following table (dbo.Users) ordered in a descending way by ID:

ID | UserID | Status | Time |
3 9200 2 2013-05-03
2 9200 1 2013-05-02
1 9200 1 2013-05-01

I want to check if the top two rows have different values for Status column, and if so, I want to get the entire first row.
How can I achieve this?

Answer Source

Assuming by "top two rows" you mean ordered by id descending, then:

select u.*
from (select top 1 u.*
      from dbo.Users u
      order by u.id desc
     ) u
where u.status <> (select top 1 u2.status
                   from dbo.Users u2
                   where u2.id < u.id
                   order by u2.id desc

The first subquery gets the top row. The second subquery determines whether the status is equal to the status in the second row.