A sql query question

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 i
mmediately before or
equal to myDate:
4 c1 01-04-04
7 c2 01-02-04
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...

quote:

---

> Hi all,
> Could anyone tell me how can I construct an sql sentence so that it can

---

show me

quote:

---

> 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,

quote:

---

> the result of the query should be all rows that have readings for the

---

date immediately before or

quote:

---

> equal to myDate:
> 4 c1 01-04-04
> 7 c2 01-02-04
>
> Thank you for all your time in advance,
> Marco.

---