Author |
Topic |
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 2007-05-13 : 17:19:35
|
Please help me in writing the query......table1: tcol1,tcol2 composite pkeys.table2:t2col1,t2col2 composite pkeys.Table3:t3col1,t3col2 composite pkeys.Conditions:eliminating the duplicates by taking the max dateTable1------tcol1 tcol2 tcol3 tcol4----- ----- ----- -----5572 1 test1 2003-01-04 00:00:00.0005572 1 test1 2003-01-05 00:00:00.0005573 1 tes4 2003-01-05 00:00:00.0005574 1 tes5 2003-01-06 00:00:00.000Table2------t2col1 t2col2 t2col3 t2col4----- ----- ----- ------5572 2 2003-01-04 00:00:00.000 Mouse5572 2 2003-01-05 00:00:00.000 desk5573 2 2003-01-05 00:00:00.000 Mousepad5574 2 2003-01-06 00:00:00.000 HwTable3------t3col1 t3col2 t3col3 t3col4----- ----- ----- ------5572 3 09 2003-01-04 00:00:00.0005572 3 10 2003-01-05 00:00:00.0005573 3 40 2003-01-05 00:00:00.0005574 3 65 2003-01-06 00:00:00.000I want the output:5572 test1 desk 10 2003-01-05 00:00:00.0005573 tes4 Mousepad 40 2003-01-05 00:00:00.0005574 tes5 Hw 65 2003-01-06 00:00:00.000 |
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-05-14 : 00:32:36
|
Select T1.tcol1, T1.tcol3, T2.t2col4, T3.t3col3, T1.tcol4From @T1 T1Inner join @T2 T2 on T2.t2col1 = T1.tcol1 and T1.tcol4 = T2.t2col3Inner join @T3 T3 on T3.t3col1 = T1.tcol1 and T1.tcol4 = T3.t3col4Where T3.t3col4 = ( Select Max(t3col4) From @T3 Where t3col1 = T1.tcol1) |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-05-14 : 00:33:54
|
Sorry...some part of solution is missed-- Prepare sample dataDeclare @T1 table (tcol1 Int, tcol2 Int, tcol3 varchar(100), tcol4 datetime)Insert into @T1Select 5572, 1, 'test1', '2003-01-04 00:00:00.000' Union allSelect 5572, 1, 'test1', '2003-01-05 00:00:00.000' Union allSelect 5573, 1, 'tes4', '2003-01-05 00:00:00.000' Union allSelect 5574, 1, 'tes5', '2003-01-06 00:00:00.000'Declare @T2 table (t2col1 Int, t2col2 Int, t2col3 Datetime, t2col4 varchar(100))Insert into @T2Select 5572, 2, '2003-01-04 00:00:00.000', 'Mouse' Union allSelect 5572, 2, '2003-01-05 00:00:00.000', 'desk' Union allSelect 5573, 2, '2003-01-05 00:00:00.000', 'Mousepad' Union allSelect 5574, 2, '2003-01-06 00:00:00.000', 'Hw'Declare @T3 Table (t3col1 Int, t3col2 Int, t3col3 Int, t3col4 Datetime )Insert into @T3Select 5572, 3, 09, '2003-01-04 00:00:00.000' Union allSelect 5572, 3, 10, '2003-01-05 00:00:00.000' Union allSelect 5573, 3, 40, '2003-01-05 00:00:00.000' Union allSelect 5574, 3, 65, '2003-01-06 00:00:00.000'Select T1.tcol1, T1.tcol3, T2.t2col4, T3.t3col3, T1.tcol4From @T1 T1Inner join @T2 T2 on T2.t2col1 = T1.tcol1 and T1.tcol4 = T2.t2col3Inner join @T3 T3 on T3.t3col1 = T1.tcol1 and T1.tcol4 = T3.t3col4Where T3.t3col4 = ( Select Max(t3col4) From @T3 Where t3col1 = T1.tcol1)Regard'sPeter |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-14 : 00:43:21
|
Alternative solutionselect tcol1, tcol3, t2col4, tcol4from @T1 inner join @T2 on tcol1 = t2col1 and tcol4 = t2col3 inner join ( select t3col1, t3col4 = max(t3col4) from @T3 group by t3col1 ) m on tcol1 = t3col1 and tcol4 = t3col4order by tcol1 KH |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2007-05-14 : 00:51:46
|
try this..Select a.tcol1, a.tcol2 , t2col4, t3col3 , t3col4from table2inner join (Select tcol1, tcol2, tcol3, max(tcol4)from Table1group by tcol1, tcol2, tcol3) aon t2col1 = a.tcol1 and t2col3 = a.tcol4inner join Table3 ont3col1 = a.tcol1 and t3col4 = a.tcol4 |
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 2007-05-15 : 10:51:43
|
Thanks !!!!!!! |
|
|
|
|
|