Joining Two Tables

18 Aug 2006 5:44 PM

c_shah

Table1

Table1ID     ControlID (int)
1            100
2            101
3            102
4            103
5            104

Table2

Table2ID     ControlID          LastChangedDate (datetime)
1            100                08/02/2006 1:30 PM
2            100                08/16/2006 3:30 PM
3            101                08/18/2006 8:15 AM
4            102                08/14/2006 8:35 AM
5            102                08/17/2006 11:14 AM
6            103                08/18/2006 1:14 PM
7            104                08/17/2006 4:32 PM

How can you join Table1 controlID with Table2 controlID with the latest
lastchangedDate or the greatest table2ID?

So my result would be

Table1ID ControlID Table2ID ControlID LastChangedDate
1        100         2        100      08/16/2006 3:30 PM

18 Aug 2006 6:08 PM

Thomas

You need to group by and use the max function.

--
Thomas

Show quote

"c_shah" wrote:

> Table1
>
> Table1ID     ControlID (int)
> 1            100
> 2            101
> 3            102
> 4            103
> 5            104
>
> Table2
>
> Table2ID     ControlID          LastChangedDate (datetime)
> 1            100                08/02/2006 1:30 PM
> 2            100                08/16/2006 3:30 PM
> 3            101                08/18/2006 8:15 AM
> 4            102                08/14/2006 8:35 AM
> 5            102                08/17/2006 11:14 AM
> 6            103                08/18/2006 1:14 PM
> 7            104                08/17/2006 4:32 PM
>
>
> How can you join Table1 controlID with Table2 controlID with the latest
> lastchangedDate or the greatest table2ID?
>
>
>
> So my result would be
>
> Table1ID ControlID Table2ID ControlID LastChangedDate
> 1        100         2        100      08/16/2006 3:30 PM
>
>

18 Aug 2006 6:58 PM

Tracy McKibben

c_shah wrote:
Show quote

> Table1
>
> Table1ID     ControlID (int)
> 1            100
> 2            101
> 3            102
> 4            103
> 5            104
>
> Table2
>
> Table2ID     ControlID          LastChangedDate (datetime)
> 1            100                08/02/2006 1:30 PM
> 2            100                08/16/2006 3:30 PM
> 3            101                08/18/2006 8:15 AM
> 4            102                08/14/2006 8:35 AM
> 5            102                08/17/2006 11:14 AM
> 6            103                08/18/2006 1:14 PM
> 7            104                08/17/2006 4:32 PM
>
>
> How can you join Table1 controlID with Table2 controlID with the latest
> lastchangedDate or the greatest table2ID?
>
>
>
> So my result would be
>
> Table1ID ControlID Table2ID ControlID LastChangedDate
> 1        100         2        100      08/16/2006 3:30 PM
>

http://realsqlguy.com/serendipity/archives/10-Gimme-The-Latest-And-Greatest!.html

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com