sql - return max value for a time period in two tables

Here is a simplified version of my problem:

I have two tables:

Students:

ST_STUDENT_ID  NAME   ST_DATE_TAKEN
-------------  -----  -------------
1              Jim    2011-01-01
2              Fred   2011-01-02
3              Sarah  2011-01-03
4              Nancy  2001-02-04

SCORES:

SC_STUDENT_ID  SC_SCORE
-------------  --------
1              97
2              97
3              95
4              97

I need to pull the student with the highest score for a month (say January). Howe开发者_如何学Gover, I only want one student even if multiple students received that score and that score could also exist outside my focus month, so this is complicating my query. The only way I could figure to do it was to redo all my criteria at each nested sub-query. Is there a better way. It wasn't too terrible here, but in my actual problem the where criteria is much more complicated and joined across many tables, duplicating it is a pain, plus the cost gets quite large.

SELECT ST_STUDENT_ID, ST_TIMESTAMP, SC_SCORE
FROM STUDENTS 
JOIN SCORES
  ON ST_STUDENT_ID = SC_STUDENT_ID
WHERE ST_STUDENT_ID = (
  SELECT MAX(ST_STUDENT_ID)
  FROM STUDENTS
  JOIN SCORES
    ON ST_STUDENT_ID = SC_STUDENT_ID
  WHERE ST_TIMESTAMP > '2011-01-01'
    AND ST_TIMESTAMP < '2011-02-01'
    AND SC_SCORE IS NOT NULL
    AND SC_SCORE = (
      SELECT MAX(SC_SCORE)
        FROM STUDENTS
      JOIN SCORES
        ON ST_STUDENT_ID = SC_STUDENT_ID
      WHERE ST_TIMESTAMP > '2011-01-01'
        AND ST_TIMESTAMP < '2011-02-01'))

---

If you only want one score, and your time period will be passed explicitly into the query, what about something like this?

SELECT TOP 1 ST_STUDENT_ID, ST_TIMESTAMP, SC_SCORE
FROM STUDENTS 
JOIN SCORES ON ST_STUDENT_ID = SC_STUDENT_ID
WHERE ST_TIMESTAMP >= '2011-01-01'
AND ST_TIMESTAMP <= '2011-02-01'
ORDER BY SC_SCORE DESC, ST_STUDENT_ID DESC

That syntax should work for MS SQL Server - different RDBMSs have slightly different syntaxes for the "TOP 1" concept.

[I see from your later comment that you're using DB2 - in which case the syntax apparently is FETCH FIRST 1 ROWS ONLY.]

Note that I'm following the logic in your example, which implies the student with the highest ID takes precedence. Good incentive to register late for class ;-)

---

(Assuming SQL Server 2005 or later, or another RDMBS that supports CTEs and window functions)

Something like:

;With OrderedScores as (
SELECT
    ST_STUDENT_ID,
    ST_TIMESTAMP,
    SC_SCORE,
    ROW_NUMBER() OVER (ORDER BY SC_SCORE desc,newid()) as rn /* Ordered randomly within same score */
FROM
    STUDENTS
        join
    SCORES
        on ST_STUDENT_ID = SC_STUDENT_ID
WHERE
    ST_TIMESTAMP >= '20110101' and
    ST_TIMESTAMP < '20110201'
)
select * from OrderedScores where rn = 1

Obviously, you can play with the criteria within the ORDER BY of the window function, to determine which student you pick when ties exist (in the above it's random; again assuming SQL Server - if another RDBMS, newid() should be replaced with something else)

In addition, I think I've got your date criteria correct here - in your original query, you have one set of criteria that use > and < (so excluding Jim), and in the other, you use <= and >=, which could include a student who tested on 1st Feb.