Dias Dias - 1 year ago 79
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download