• 0

SQL Select Statement Question


Question

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

  • 0

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

  • 0

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

  • 0

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

  • 0
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

  • 0
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

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.