How to this with SQL

Table and sample data is
id date name value1
1 15-11-05 a 5
2 15-11-05 b 2
3 16-11-05 a 3
4 17-11-05 a 7
5 18-11-05 a 5
6 18-11-05 b 1
If date parameter is 17-11-05
result is
17-11-05 a 7
15-11-05 b 2
If date parameter is 18-11-05
result is
18-11-05 a 5
18-11-05 b 1
thanksIf you are looking for rows where the entry is the most-recent on or before
the given date for each name, then try:
select
m.*
from
(
select
name
, max ([date]) [date]
from
MyTable
where
[date] <= @.The Date
group by
name
) x
join MyTable m on m.name = x.name
and m.[date] = x.[date]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Serhat AKALIN" <serhatakalin@.yahoo.com> wrote in message
news:umDMcbt7FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Table and sample data is
> id date name value1
> 1 15-11-05 a 5
> 2 15-11-05 b 2
> 3 16-11-05 a 3
> 4 17-11-05 a 7
> 5 18-11-05 a 5
> 6 18-11-05 b 1
> If date parameter is 17-11-05
> result is
> 17-11-05 a 7
> 15-11-05 b 2
> If date parameter is 18-11-05
> result is
> 18-11-05 a 5
> 18-11-05 b 1
>
> thanks
>|||select thetable.date, thetable.name, thetable.value1
from thetable
join (
select name, max(date) as date
from thetable
where date<=@.DateParameter
group by name
) maxdates
on thetable.name=maxdates.name
and thetable.date=maxdates.date
order by thetable.name
Serhat AKALIN wrote:
> Table and sample data is
> id date name value1
> 1 15-11-05 a 5
> 2 15-11-05 b 2
> 3 16-11-05 a 3
> 4 17-11-05 a 7
> 5 18-11-05 a 5
> 6 18-11-05 b 1
> If date parameter is 17-11-05
> result is
> 17-11-05 a 7
> 15-11-05 b 2
> If date parameter is 18-11-05
> result is
> 18-11-05 a 5
> 18-11-05 b 1
>
> thanks
>