find MAX value of a field among multiple tables

[yuchieh]

I have 9 tables in a DB and there is a dateUpadated field in each table. We need to find and populate the most recent date in this field and compare among all these 9 tables.

How should I do this in a stored proecdure?

Thank you

 

[djj55]

Hello,
Are you saying the value is null and you need to populate the field?

How do you want to compare the data?

djj

 

[yuchieh]

value is not null. there are date in each dateUpaded field.

For example,
max(table1.dateUpdated) = '11/5/2007'
max(table2.dateUpdated) = '11/7/2007'
.
.
max(table9.dateUpdated) = '11/1/2007'

How do I compare these 9 dates and find the maximum one?

thanks

 

[kaht]

Here's an example using 3 tables, but it's easily scaled to work for 9:

[COLOR=blue]declare[/color] @a [COLOR=blue]table[/color] (blah [COLOR=blue]int[/color])
[COLOR=blue]declare[/color] @b [COLOR=blue]table[/color] (blah [COLOR=blue]int[/color])
[COLOR=blue]declare[/color] @c [COLOR=blue]table[/color] (blah [COLOR=blue]int[/color])

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a
[COLOR=blue]select[/color] 1 union
[COLOR=blue]select[/color] 2 union
[COLOR=blue]select[/color] 3

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @b
[COLOR=blue]select[/color] -1 union
[COLOR=blue]select[/color] -2 union
[COLOR=blue]select[/color] -3

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @c
[COLOR=blue]select[/color] 0 union
[COLOR=blue]select[/color] 5 union
[COLOR=blue]select[/color] 69

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @a
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @b
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @c

[COLOR=blue]select[/color] top 1 tbl, blah
[COLOR=blue]from[/color] (
   [COLOR=blue]select[/color] [COLOR=red]'a'[/color] tbl, blah [COLOR=blue]from[/color] @a union all
   [COLOR=blue]select[/color] [COLOR=red]'b'[/color] tbl, blah [COLOR=blue]from[/color] @b union all
   [COLOR=blue]select[/color] [COLOR=red]'c'[/color] tbl, blah [COLOR=blue]from[/color] @c
) a
[COLOR=blue]order[/color] [COLOR=blue]by[/color] blah [COLOR=#FF00FF]desc[/color]

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[kaht]

Sorry, didn't realize you said dates - I used integers. But, the concept is the same.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[gmmastros]

kaht,

That is exactly what I would have suggested.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kaht]

That is exactly what I would have suggested.

Guess that means I'm getting better

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[yuchieh]

do you mind explaining this part

insert into @a
select 1 union
select 2 union
select 3

what are those 3 selects?

thanks

 

[SQLSister]

that was a sneaky way of populating the table variable with three records in one step rather than three inserts using the values clause.

"NOTHING is more important in a database than integrity." ESquared

 

[kaht]

Sorry, was AFK. SQLSister is right, it's just a shortcut to populate the example data. Using union, you can combine multiple queries to return 1 result set.

[COLOR=blue]select[/color] [COLOR=red]'1'[/color] union [COLOR=blue]select[/color] [COLOR=red]'2'[/color] union [COLOR=blue]select[/color] [COLOR=red]'3'[/color] union [COLOR=blue]select[/color] [COLOR=red]'4'[/color] union [COLOR=blue]select[/color] [COLOR=red]'etc'[/color]

That's actually the "trick" to returning the max result out of multiple tables - union them all together so that you're only pulling the max of 1 result set.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[yuchieh]

I did it and it works. learn something new; feels good.

Thank you all so very much.