[thelist] SQL Challenge Question

[Rob Smith]

<snip Author="Jay Blanchard">
If you are using an auto-increment number of some sort you could always
get the latest one via the highest auto-increment number.
</snip>

Thanks Jay. I've done all that. Rudy and I've been going back and forth on
this [off list] and he gave me:

select P1.Project
     , '>'            as Diffs
     , P1.ProjectID
     , case when P1.ProjectVersion 
              <> P2.ProjectVersion
            then '*' else ' ' end as ProjectVersion   
     , case when P1.ProjectOwner 
              <> P2.ProjectOwner
            then '*' else ' ' end as ProjectOwner   
     , case when P1.ProjectTitle 
              <> P2.ProjectTitle
            then '*' else ' ' end as ProjectTitle   
     , case when P1.ProjectDesc 
              <> P2.ProjectDesc
            then '*' else ' ' end as ProjectDesc   
     , case when P1.ProjectCompetition 
              <> P2.ProjectCompetition
            then '*' else ' ' end as ProjectCompetition   
     , case when P1.ProjectMemberID 
              <> P2.ProjectMemberID
            then '*' else ' ' end as ProjectMemberID   
     , case when P1.ProjectRemarkID 
              <> P2.ProjectRemarkID
            then '*' else ' ' end as ProjectRemarkID   
     , case when P1.ProjectAffiliateID 
              <> P2.ProjectAffiliateID
            then '*' else ' ' end as ProjectAffiliateID   
     , case when P1.ProjectTimeID 
              <> P2.ProjectTimeID
            then '*' else ' ' end as ProjectTimeID   
     , case when P1.ProjectEngID 
              <> P2.ProjectEngID
            then '*' else ' ' end as ProjectEngID   
  from GPT.dbo.Projects P1
inner
  join GPT.dbo.Projects P2
    on P1.Project = P2.Project
 where P1.Project = 'AP-12345' 
   and P1.ProjectVersion = '1'         
   and P2.ProjectVersion = '2'         
union all
select Project
     , ' '     
     , ProjectID
     , cast(ProjectVersion as varchar(15))
     , ProjectOwner
     , ProjectTitle
     , ProjectDesc
     , ProjectCompetition
     , cast(ProjectMemberID as varchar(15))
     , cast(ProjectRemarkID as varchar(15))
     , cast(ProjectAffiliateID as varchar(15))
     , cast(ProjectTimeID as varchar(15))
     , cast(ProjectEngID as varchar(15))
  from GPT.dbo.Projects
 where Project = 'AP-12345' 
   and ProjectVersion = '1'
union all
select Project
     , ' '        
     , ProjectID
     , cast(ProjectVersion as varchar(15))
     , ProjectOwner
     , ProjectTitle
     , ProjectDesc
     , ProjectCompetition
     , cast(ProjectMemberID as varchar(15))
     , cast(ProjectRemarkID as varchar(15))
     , cast(ProjectAffiliateID as varchar(15))
     , cast(ProjectTimeID as varchar(15))
     , cast(ProjectEngID as varchar(15))
  from GPT.dbo.Projects
 where Project = 'AP-12345' 
   and ProjectVersion = '2'
order
    by 1 -- project
     , 2 -- diffs
     , 3 -- projectid

Which creates a new row and places an * in the column where the two rows are
different per column. 

Rob