thevink Posted August 17, 2009 Share Posted August 17, 2009 Should be simple to answer: Table1: ID Name 1 Ted 2 Steve 3 Paul Table 2: ID Food Date 1 Taco 9/1/2009 1 Beef 8/1/2009 1 Yogurt 7/1/2009 2 Apple 8/1/2009 2 Pear 6/1/2009 Desired Output: ID Name Food Date 1 Ted Taco 9/1/2009 2 Steve Apple 8/1/2009 (basically i want the most recent date's entry to display) Any thoughts? Link to comment Share on other sites More sharing options...
0 Rohdekill Posted August 18, 2009 Share Posted August 18, 2009 one way: declare @min as int, @max as int, @Highdate as smalldatetime create table #TEMP (ID int, [Name] nvarchar(50), Food nvarchar(50), [Date] smalldatetime) set @min = (select min(ID) from [Table1]) set @max = (select max(ID) from [Table1]) while @min<= @max BEGIN set @highdate=(select max([Date]) from [Table2] where ID = @min) if len(@Highdate)>0 BEGIN insert into #TEMP select [Table2].id, [Name], Food, [Date] from [Table2] inner join [Table1] on [Table1].ID = [Table2].ID where [Date] = @highdate and [Table2].ID = @min END set @min = (select min(ID) from [Table1] where ID > @min) END select * from #TEMP Result: 1 Ted Taco 2009-09-01 00:00:00 2 Steve Apple 2009-08-01 00:00:00 assuming this was a homework assignment... It would have been easier had table 2 had a new integer column that has an identity seed. As entries are made, you know the most current is always the highest seeded entry. Link to comment Share on other sites More sharing options...
0 Glen Posted August 18, 2009 Share Posted August 18, 2009 Maybe something like this (not tested): select t1.ID, t1.Name, t2.Food, t2.Date from Table1 as t1 inner join Table2 as t2 on t2.ID = t1.ID where t2.Date = ( select max(Date) from Table2 where ID = t1.ID ) Theoretically it should work but I don't have SQL server on my home PC to test it. Hope this helps. Link to comment Share on other sites More sharing options...
0 atrotter01 Posted August 19, 2009 Share Posted August 19, 2009 This will work, basically the same thing Glen posted but he beat me to it :) create table t1 ( id int, name text ); create table t2 ( id int, food text, date date ); insert into t1 ( id, name ) values (1,'Ted'), (2,'Steve'), (3,'Paul'); insert into t2 ( id, food, date ) values (1,'Taco','2009-09-01'), (1,'Beef','2009-08-01'), (1,'Yogurt','2009-07-01'), (2,'Apple','2009-08-01'), (2,'Pear','2009-06-01'); select t1.id, t1.name, t2.food, t2.date from t1 join t2 using(id) where t2.date = (select max(date) from t2 where id=t1.id ) group by t1.id order by t1.id; Link to comment Share on other sites More sharing options...
0 Rohdekill Posted August 19, 2009 Share Posted August 19, 2009 Maybe something like this (not tested):select t1.ID, t1.Name, t2.Food, t2.Date from Table1 as t1 inner join Table2 as t2 on t2.ID = t1.ID where t2.Date = ( select max(Date) from Table2 where ID = t1.ID ) Theoretically it should work but I don't have SQL server on my home PC to test it. Hope this helps. I initially headed this direction but was too tired from work to think clearly. I knew there was an easier way. Link to comment Share on other sites More sharing options...
0 thevink Posted August 19, 2009 Author Share Posted August 19, 2009 Maybe something like this (not tested):select t1.ID, t1.Name, t2.Food, t2.Date from Table1 as t1 inner join Table2 as t2 on t2.ID = t1.ID where t2.Date = ( select max(Date) from Table2 where ID = t1.ID ) Theoretically it should work but I don't have SQL server on my home PC to test it. Hope this helps. Perfect. Works like a charm, thanks! Link to comment Share on other sites More sharing options...
Question
thevink
Should be simple to answer:
Table1:
ID Name
1 Ted
2 Steve
3 Paul
Table 2:
ID Food Date
1 Taco 9/1/2009
1 Beef 8/1/2009
1 Yogurt 7/1/2009
2 Apple 8/1/2009
2 Pear 6/1/2009
Desired Output:
ID Name Food Date
1 Ted Taco 9/1/2009
2 Steve Apple 8/1/2009
(basically i want the most recent date's entry to display)
Any thoughts?
Link to comment
Share on other sites
5 answers to this question
Recommended Posts