Finding the most recently modified row from a group of tables

Question

Post reply

Finding the most recently modified row from a group of tables

rray 44280

Ten Centuries

Points: 1112
More actions
July 11, 2013 at 11:07 am

#280225

I'm thinking there's some clever way of doing this, probably involving a CTE and recursion, but I'm not seeing how to get started....
Given a [Table1] with a primary key called [Table1Key].
There are 30 tables with [Table1Key] as a foreign key pointing back to [Table1], call these [RTable01] through [RTable30]. Any specific Table1 row may have related rows in 0 to 30 of these tables.
Each of the related tables has a [LastModifiedDate], as does the parent Table1 row.
Given two Table1 rows, I want to flag one as 'Most Current' based on the latest of ANY of the LastModifiedDates in related rows and the parent row.
I can certainly do this in a brute force ROAR fashion, but I'm trying to stop doing things like that. Any pointers?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

Certainly no need to post ALL 30 tables but maybe 2 or 3 would illustrate what you are trying to here?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

Something like this should give you the most recent LastModifiedDate for each Table1Key.

select Table1Key, MAX(LastModifiedDate)

from Table1

outer apply (

select Table1.LastModifiedDate

union all

select LastModifiedDate

from RTable01

where RTable01.Table1Key = Table1.Table1Key

union all

select LastModifiedDate

from RTable02

where RTable02.Table1Key = Table1.Table1Key

...

union all

select LastModifiedDate

from RTable30

where RTable30.Table1Key = Table1.Table1Key

) AllTables(LastModifiedDate)

group by Table1.Table1Key

However, as Sean said, if you want a tested solid answer, post the information requested 😉

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

rray 44280 Ten Centuries Points: 1112 More actions · Answer 3

Does this give you what you need?

--------------------------

--DDL

--------------------------

CREATE SCHEMA [SAMPLE]

GO

IPCode is PK

CREATE TABLE [sample].127.0.0.1(

[IPCode] [int] NOT NULL,

[UpdateDate] [datetime] NULL)

GO

--IPCode is FK

CREATE TABLE [sample].[AddressProfile](

[IPCode] [int] NOT NULL,

[UpdateDate] [datetime] NULL)

GO

--IPCode is FK

CREATE TABLE [sample].[EmailProfile](

[IPCode] [int] NOT NULL,

[UpdateDate] [datetime] NULL)

GO

--IPCode is FK

CREATE TABLE [sample].[PhoneProfile](

[IPCode] [int] NOT NULL,

[UpdateDate] [datetime] NULL)

GO

--------------------------

--Sample data inserts

--------------------------

INSERT INTO [SAMPLE].127.0.0.1( [UpdateDate] ) VALUES(1000000,'2013-07-11 18:51:32')

INSERT INTO [SAMPLE].127.0.0.1( [UpdateDate] ) VALUES(1000001,'2012-07-11 18:51:32')

INSERT INTO [SAMPLE].127.0.0.1( [UpdateDate] ) VALUES(1000002,'2011-07-11 18:51:32')

INSERT INTO [SAMPLE].127.0.0.1( [UpdateDate] ) VALUES(1000003,'2010-07-11 18:51:32')

INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000000,'2013-06-11 18:54:24')

INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000000,'2013-04-11 18:54:24')

INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000001,'2013-05-11 18:54:24')

INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000001,'2013-02-11 18:54:24')

INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000001,'2013-03-11 18:54:24')

INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000002,'2013-11-11 18:54:24')

INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000002,'2013-12-11 18:54:24')

INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000003,'2013-09-11 18:54:24')

INSERT INTO [SAMPLE].[AddressProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000003,'2013-06-11 18:54:24')

INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000000,'2013-06-21 18:54:24')

INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000000,'2013-04-23 18:54:24')

INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000001,'2013-05-02 18:54:24')

INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000001,'2013-02-12 18:54:24')

INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000001,'2013-03-05 18:54:24')

INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000002,'2013-11-07 18:54:24')

INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000002,'2013-12-02 18:54:24')

INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000003,'2013-09-09 18:54:24')

INSERT INTO [SAMPLE].[EmailProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000003,'2013-06-10 18:54:24')

INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000000,'2010-06-02 18:54:24')

INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000000,'2012-04-05 18:54:24')

INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000001,'2012-05-12 18:54:24')

INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000001,'2012-02-08 18:54:24')

INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000001,'2012-03-09 18:54:24')

INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000002,'2012-11-10 18:54:24')

INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000002,'2012-12-11 18:54:24')

INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000003,'2012-09-01 18:54:24')

INSERT INTO [SAMPLE].[PhoneProfile] ( [IPCode], [UpdateDate] ) VALUES( 1000003,'2012-06-02 18:54:24')

Sample output would look like:

myFunction(1000000) returns '2013-07-11 18:51:32'

myFunction(1000001) returns '2013-05-11 18:54:24'

myFunction(1000002) returns '2013-12-11 18:54:24'

myFunction(1000003) returns '2013-09-11 18:54:24'

IP is the parent table, the profiles are the 0 to 30 related tables from which I need to extract the earliest date. The column names are consistent across all tables.

rray 44280 Ten Centuries Points: 1112 More actions · Answer 4

Ah ha, OUTER APPLY....

I've never use that. I barely know it exists. Can you give me any good links to help figuring out how it works? I can sort of see what's going on, but I haven't grokked the fullness.

Now I get to figure out something new! Thanks!

p.s. The requested DDL and data inserts are posted.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Thanks for the ddl and sample data. Using MM's excellent example...

select IPCode, Max(LastUpdateDate)

from

IP i

outer apply

(

select UpdateDate from IP where IPCode = i.IPCode

union all

select UpdateDate from AddressProfile where IPCode = i.IPCode

union all

select UpdateDate from EmailProfile where IPCode = i.IPCode

union all

select UpdateDate from PhoneProfile where IPCode = i.IPCode

) x (LastUpdateDate)

group by i.IPCode

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101126 More actions · Answer 6

SELECT IP.IPCode, MAX(AllSubtables.UpdateDate) AS LastUpdateDate

FROM SAMPLE.IP IP

INNER JOIN (

SELECT IPCode, MAX(UpdateDate) AS UpdateDate FROM SAMPLE.IP IP2 GROUP BY IP2.IPCode

UNION ALL

SELECT IPCode, MAX(UpdateDate) AS UpdateDate FROM SAMPLE.AddressProfile AP GROUP BY AP.IPCode

UNION ALL

SELECT IPCode, MAX(UpdateDate) AS UpdateDate FROM SAMPLE.EmailProfile EP GROUP BY EP.IPCode

UNION ALL

SELECT IPCode, MAX(UpdateDate) AS UpdateDate FROM SAMPLE.PhoneProfile PP GROUP BY PP.IPCode

--UNION ALL ...

) AS AllSubtables ON

AllSubtables.IPCode = IP.IPCode

GROUP BY

IP.IPCode

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".