More efficient query than this?

I have two tables:
tblA tblB
---
ColA int PK ColA int FK
... ColB int PK
DateAdded datetime
where tblB/ColA references tblA/ColA.
I need to find the most recently added row from tblB given a value of
ColA.
I wrote the following but wondered if there was a better, more
efficient method.
declare @.ColA int
select ColB, p.dateadded
from tblB b
inner join tblA a on b.ColA = a.ColA
where a.ColA = @.ColA and
b.dateadded = (select max(b.dateadded)
from tblB b
inner join tblA a on b.ColA = a.ColA
where a.ColA = @.ColA)
TIA LarsMhhm,
if there is a constraint between the two why do you need the Join ?
declare @.ColA int
select ColB, MAX(p.dateadded)
from tblB b
where b.ColA = @.ColA
Group by ColB
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:vtd061tnman8e1hft4f23rfos4sqa1q9a8@.
4ax.com...
>I have two tables:
> tblA tblB
> ---
> ColA int PK ColA int FK
> ... ColB int PK
> DateAdded datetime
> where tblB/ColA references tblA/ColA.
> I need to find the most recently added row from tblB given a value of
> ColA.
> I wrote the following but wondered if there was a better, more
> efficient method.
> declare @.ColA int
> select ColB, p.dateadded
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA and
> b.dateadded = (select max(b.dateadded)
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA)
> TIA Lars
>|||Jens,
Thanks for the reply. I don't think I explained the problem well
enough.
The table definition
tblA tblB
---
ColA int PK ColA int FK
... ColB int PK
DateAdded datetime
and some test data
ColA ColB DateAdded
---
100 32 2-10-2005
100 16 3-01-2005
100 24 3-16-2005
100 20 1-15-2005
101 ...
I would like a query which would return the single row whose ColA
value = 100
100 24 3-16-2005
since it has the latest date.
Lars
On Sat, 16 Apr 2005 00:17:02 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>Mhhm,
>if there is a constraint between the two why do you need the Join ?
>declare @.ColA int
>select ColB, MAX(b.dateadded)
>from tblB b
>where b.ColA = @.ColA
>Group by ColB
>HTH, Jens Smeyer
>--
>http://www.sqlserver2005.de
>--
>
>"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
> news:vtd061tnman8e1hft4f23rfos4sqa1q9a8@.
4ax.com...
>|||OK, whats wrong with that?
declare @.ColA int
Set ColA = 100
select b.ColB, MAX(b.dateadded)
from tblB b
where b.ColA = @.ColA
Group by ColB
Jens Smeyer.
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:e9i061t1nrhddkviiksi0vf3qdk3m2g3qo@.
4ax.com...
> Jens,
> Thanks for the reply. I don't think I explained the problem well
> enough.
> The table definition
> tblA tblB
> ---
> ColA int PK ColA int FK
> ... ColB int PK
> DateAdded datetime
> and some test data
> ColA ColB DateAdded
> ---
> 100 32 2-10-2005
> 100 16 3-01-2005
> 100 24 3-16-2005
> 100 20 1-15-2005
> 101 ...
> I would like a query which would return the single row whose ColA
> value = 100
> 100 24 3-16-2005
> since it has the latest date.
> Lars
>
> On Sat, 16 Apr 2005 00:17:02 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>|||It returns all 4 rows when I expect only 1.
On Sat, 16 Apr 2005 01:31:54 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>OK, whats wrong with that?
>declare @.ColA int
>Set ColA = 100
>select b.ColB, MAX(b.dateadded)
>from tblB b
>where b.ColA = @.ColA
>Group by ColB
>Jens Smeyer.
>
>"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
> news:e9i061t1nrhddkviiksi0vf3qdk3m2g3qo@.
4ax.com...
>|||Sorry youre right, forget about that, try this.
Select * from TableB Where
COLB =
(
Select TOP 1 COLB
From TableB
WHERE COLA = 100
Group by COLB
Order by MAX(dateadded) DESC
)
HTH (now), Jens Smeyer ;-)
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:j5k061hvmpdqbgjog7nf78qrq9ms5ki73k@.
4ax.com...
> It returns all 4 rows when I expect only 1.
> On Sat, 16 Apr 2005 01:31:54 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>|||Try This...
Select ColA, ColB, DateAdded
From TblB B
Where DateAdded =
(Select Max(DateAdded)
From TblB
Where ColA = B.ColA)
And ColA = @.ColA
Leave out the last row ( And ColA = @.ColA) to get the results for all ColA
values
"larzeb" wrote:

> I have two tables:
> tblA tblB
> ---
> ColA int PK ColA int FK
> .... ColB int PK
> DateAdded datetime
> where tblB/ColA references tblA/ColA.
> I need to find the most recently added row from tblB given a value of
> ColA.
> I wrote the following but wondered if there was a better, more
> efficient method.
> declare @.ColA int
> select ColB, p.dateadded
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA and
> b.dateadded = (select max(b.dateadded)
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA)
> TIA Lars
>
>sql