2 views to one 1

[aumstu]

Hello everyone…I have another sql question. I am checking a database of everyone that has a previous name. For example, change of name after marriage. The table has the following columns.

ID   FirstName   LastName   Change_Indicator  Activity_Date
1     Billy      Bob        NULL              2006-02-24
1     Billy      XYZ         N                2005-02-24
1     Billy      ABC         N                2004-02-24

I need just to select the max activity date with a change indicator of N, which is Billy XYZ. For me to do this I create two views. The first view selects the ID, max activity date, where the change indicator is N. Then in the second view I link the ID, Activity Date, and Change Indicator to get the answer.

Does anyone know of a quicker way to do this in one sql statement? In access sometimes I would use the first(column name) to get the first record…but in sql server this does not exist. Plus, if I use the first(column) in this problem I am scared it will not pull maximum date.

Thanks for any input…and I enjoy reading the puzzles…I am not ready to compute with you sql gurus yet

 

[SQLDenis]

select t1.* from table1 t1 join (select max(Activity_Date) as maxDate,id from table1 where indicator ='N' group by id) x
on t1.id =x.id
and t1.Activity_Date =x.maxDate

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aumstu]

Thanks for the help...that worked out perfect