How to check to see if two rows have same or diff

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to check to see if two rows have same or diff

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2014-09-11 : 10:57:22

Hi,

I have the query and it always returned two records, but the statusInsp may not be the same. For example,
Case 1: statusInsp shows 1 and 1 for both two records
Case 2: statusInsp shows 2 and 2 for both two records
Case 3: statusInsp shows 1 and 2 for both two records (see the result below)

I want to add the new field from the select statement and check to see
if case 1 then show 'Y'
case 2 then show 'N'
case 3 then show 'other'
Can anyone help me with this?, how to check 2 records at the same time?- thanks
================================

select * from (
select
ROW_NUMBER() OVER(ORDER BY a.dateed DESC) AS Row,
a.so,a.statusInsp
from pur46 a
inner join pur40 b
on a.so = b.so
where b.itm = '075653A'
group by a.so,a.dateed,a.statusInsp
) AS XXXX
where Row in (1,2)

Result shows:
Row so statusInsp
----- ------ -----
1 SO856 1
2 SO741 2

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 11:34:59

"how to check 2 records at the same time?- thanks"

IN SQL Server 2012, you can use the LEAD/LAG functions to look at following/preceding rows.

http://msdn.microsoft.com/en-us/library/hh231256.aspx

IN earlier versions, you need a self-join on table1.row = table2.row-1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources