Sql-server – Does this query make sense

sql serversql-server-2008

I recently migrated a database from Sybase ASE to Microsoft SQL 2008r2.
One category of queries that was broken in transit is:

SELECT person_id
  FROM leaveperiods
 WHERE group_id=146
 GROUP BY person_id
HAVING start_date = max(start_date)

According to Sybase, this is a valid query. But Microsoft SQL is stricter, so I get this error:

Column 'leaveperiods.start_date' is invalid in the HAVING clause
because it is not contained in either an aggregate function or the GROUP BY clause.

While looking at this query to try and correct it, I realized that it does not make sense to me. Isn't this exactly the same as the below?

SELECT DISTINCT person_id
  FROM leaveperiods
 WHERE group_id=146

Or maybe I should write like this?

SELECT DISTINCT person_id
  FROM leaveperiods
 WHERE group_id=146
   AND start_date IS NOT NULL

There are several queries like this in one application and I need to fix them without breaking anything else. Please help.

Best Answer

As far as I understand the semantics of the Sybase non standard GROUP BY a purely mechanical rewrite would be.

WITH T
     AS (SELECT person_id,
                start_date,
                MAX(start_date) OVER (PARTITION BY person_id) AS max_start_date
         FROM   leaveperiods
         WHERE  group_id = 146)
SELECT person_id
FROM   T
WHERE  start_date = max_start_date

But the query does seem odd.

Documentation Extract

For example, many versions of SQL do not allow the inclusion of the extended title_id column in the select list, but it is legal in Transact-SQL:

SELECT type,
       title_id,
       avg(price),
       avg(advance)
FROM   titlesgroup
GROUP  BY type

The above example still aggregates the price and advance columns based on the type column, but its results also display the title_id for the books included in each group.

+--------------+----------+------------+--------------+
|     type     | title_id | avg(price) | avg(advance) |
+--------------+----------+------------+--------------+
| mod_cook     | MC3021   | 11.49      | 7,500.00     |
| UNDECIDED    | MC3026   | NULL       | NULL         |
| popular_comp | PC1035   | 21.48      | 7,500.00     |
| popular_comp | PC8888   | 21.48      | 7,500.00     |
| popular_comp | PC9999   | 21.48      | 7,500.00     |
| psychology   | PS1372   | 13.50      | 4,255.00     |
| psychology   | PS2091   | 13.50      | 4,255.00     |
| psychology   | PS2106   | 13.50      | 4,255.00     |
| psychology   | PS3333   | 13.50      | 4,255.00     |
| psychology   | PS7777   | 13.50      | 4,255.00     |
| trad_cook    | TC3218   | 15.96      | 6,333.33     |
| trad_cook    | TC4203   | 15.96      | 6,333.33     |
| trad_cook    | TC7777   | 15.96      | 6,333.33     |
+--------------+----------+------------+--------------+
Related Question