March 28, 2014 at 2:06 am
Hi All,
Suppose i have Two tables... with both the table having LastUpdated Column.
And, in my Select Query i m using both the table with inner join.
And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).
Thanks in advance.
March 28, 2014 at 4:59 am
Hi Guys,
TABLE_A
ID NAME LastUpdateDate
1 A 2014/01/01
2 B 2014/01/01
TABLE_B
ID NAME LastUpdateDate
1 A 2013/01/01
2 B 2015/01/01
Required Result Set
ID NAME MaxLastUpdateDate
1 A 2014/01/01
2 B 2015/01/01
March 28, 2014 at 5:03 am
Looking for Immediate Help
March 28, 2014 at 6:04 am
You can use ORDER BY along with TOP 1 to get the row that has the highest value.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 28, 2014 at 2:26 pm
SELECT
COALESCE(a.ID, b.ID) AS ID,
COALESCE(a.NAME, b.NAME) AS NAME,
CASE WHEN a.UpdateDate > b.UpdateDate THEN a.UpdateDate ELSE b.UpdateDate END AS LastUpdateDate
FROM TABLE_A a
FULL OUTER JOIN TABLE_B b ON
b.NAME = a.NAME
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 29, 2014 at 11:42 am
Missing additional details, such as what columns are used to link the two tables, is it the ID column or the NAME column? Is there always a one to one relationship between the tables or is it possible that information may only exist in one of the tables and if so how to handle that situation.
Please note how I setup the tables and sample data. This is what you should provide to us.
The solution provided is based solely on the data you provided so may not reflect what you actually require.
if object_id('dbo.TableA','U') is not null
drop table dbo.TableA;
if object_id('dbo.TableB','U') is not null
drop table dbo.TableB;
create table dbo.TableA (
ID int,
NAME varchar(4),
LastupdatedDate date
);
create table dbo.TableB (
ID int,
NAME varchar(4),
LastupdatedDate date
);
insert into dbo.TableA
values (1,'A','2014-01-01'),(2,'B','2014-01-01');
insert into dbo.TableB
values (1,'A','2013-01-01'),(2,'B','2015-01-01');
go
select
a.ID,
a.NAME,
case when a.LastupdatedDate >= b.LastupdatedDate
then a.LastupdatedDate
else b.LastupdatedDate
end MaxLastUpdateDate
from
dbo.TableA a
inner join dbo.TableB b
on (a.ID = b.ID);
go
drop table dbo.TableA;
drop table dbo.TableB;
go
[/code]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply