[RESOLVED] Need help with SqlCE select query

I have three tables that are combined into one datatable using this query and I have problems with the part highlighted in red:

Code:

---


SELECT CONTRACTORS.ID,
          CONTRACTORS.LASTNAME + ' ' + CONTRACTORS.NAME + ' ' + CONTRACTORS.MIDDLENAME AS CONTFULLNAME,
                CONTRACTORS.CONTRACT, FIRMS.FIRMID, FIRMS.SHORTNAME AS FIRMSHORTNAME, FIRMS.FULLNAME AS FIRMFULLNAME,
                DIRECTORS.LASTNAME + ' ' + DIRECTORS.NAME + ' ' + DIRECTORS.MIDDLENAME AS DIRECTORFULL,
                DIRECTORS.LASTNAME + ' ' + SUBSTRING(DIRECTORS.NAME, 1, 1) + '. ' + SUBSTRING(DIRECTORS.MIDDLENAME, 1, 1)
                + '.' AS DIRECTORSHORT
FROM      CONTRACTORS INNER JOIN
                FIRMS ON CONTRACTORS.FIRM = FIRMS.FIRMID INNER JOIN
                DIRECTORS ON FIRMS.FIRMID = DIRECTORS.FIRM
WHERE  (DIRECTORS.ONDUTYSINCE <= @pDate)
ORDER BY CONTFULLNAME

---

Basically I need to get the LATEST (active) director of the firm (some firms have history of two-three directors). The distinctive field is 'ONDUTYSINCE' which has the appointment date of a new director. Now I only check whether the appointment date is less than some date parameter I pass, but the query returns all directors who were active before, not just the latest one.
How should I modify this query to get only LAST one but ignore those who were appointed AFTER the pDate.

Example:

Code:

---

+------------+---------------+------+
| DIRECTOR  | ONDUTYSINCE  | FIRM |
+------------+---------------+------+
| John      |  01/01/2008  |    1 |
+------------+---------------+------+
| Peter      |  05/03/2009  |    1 |
+------------+---------------+------+
| Donald    |  01/01/2009  |    2 |
+------------+---------------+------+
| Alex      |  02/04/2010  |    1 |
+------------+---------------+------+
| David      |  01/01/2010  |    2 |
+------------+---------------+------+

---

Say, if I need to know who was the director of the firm #1 on June 1st, 2009 what should I write into a select query? (I need only PETER in this example)

Is it possible with a SINGLE query in SqlCE?

Would adding TOP 1 not work?

Code:

---

SELECT TOP 1 CONTRACTORS.ID,

---

It's SqlCE. Top clause is not available.

I think you need some sort of subquery/view to find out the date closest to the parameter. This is not correct syntax, but something like this...

SELECT firm, MAX(ondutysince)
where ondutysince <= @param
group by firm

Well, the question was whether this was possible using ONE query. Apparently not and I simply used two queries instead of one. Thus, I suppose, the issue is resolved.