A sql query question

Hi all
Could anyone tell me how can I construct an sql sentence so that it can show m
the latest readings for counters, given a date
To get the idea clear, let me show the table I have
Table: countin
ID COUNTER COUNTING_DAT
1 c1 01-01-0
2 c1 01-02-0
3 c1 01-03-0
4 c1 01-04-0
5 c1 01-05-0
6 c2 01-01-0
7 c2 01-02-0
I need the rows that have the very previous readings given a date, i.e. if I have myDate=01-04-04,
the result of the query should be all rows that have readings for the date immediately before or
equal to myDate
4 c1 01-04-0
7 c2 01-02-0
Thank you for all your time in advance
Marco.SELECT id, counter, counting_date
FROM Counting AS C
WHERE counting_date = (SELECT MAX(counting_date)
FROM Counting
WHERE counting_date <= @.MyDate
AND counter = C.counter)
--
David Portas
SQL Server MVP
--|||Try this:
SELECT A.Counter,
(SELECT TOP 1 Counting_date FROM Your_Table
WHERE Counting_date <= '1/4/2004'
AND Counter = A.Counter
ORDER BY Counting_date DESC)
FROM Your_Table A
GROUP BY A.Counter
--
Rohtash Kapoor
http://www.sqlmantra.com
"Oysterec" <anonymous@.discussions.microsoft.com> wrote in message
news:A13B76B6-5523-45DB-8C85-9F59B20A809C@.microsoft.com...
> Hi all,
> Could anyone tell me how can I construct an sql sentence so that it can
show me
> the latest readings for counters, given a date?
> To get the idea clear, let me show the table I have:
> Table: counting
> ID COUNTER COUNTING_DATE
> 1 c1 01-01-04
> 2 c1 01-02-04
> 3 c1 01-03-04
> 4 c1 01-04-04
> 5 c1 01-05-04
> 6 c2 01-01-04
> 7 c2 01-02-04
> I need the rows that have the very previous readings given a date, i.e. if
I have myDate=01-04-04,
> the result of the query should be all rows that have readings for the
date immediately before or
> equal to myDate:
> 4 c1 01-04-04
> 7 c2 01-02-04
>
> Thank you for all your time in advance,
> Marco.