1

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.

5
  • Are you just trying to find the people with the highest start date for that group? Can you show some sample data and desired results? Also I suggest making a more descriptive title. "Does this query make sense?" is meaningless and could apply to any query-related question on the site. Commented Mar 7, 2013 at 15:58
  • The "trying to do" I don't know. There is this big web-based PHP application that worked ok in Sybase, and was issuing this query. So what I actually try to do is to reproduce the same behaviour of this query, in a way that is accepted by Mssql. Then there is the personal learning part, I am trying to understand if there is something in this query that I don't understand. Commented Mar 7, 2013 at 16:08
  • 1
    Actually even with the Sybase non standard GROUP BY it doesn't make sense to me that you would SELECT the same column as you are grouping by with that HAVING clause. Commented Mar 7, 2013 at 16:15
  • That was my impression too. Commented Mar 7, 2013 at 16:23
  • I think MSSQL might require any columns in your "having" clause to be in your grouping, so I think you might have to change your grouping clause to GROUP BY person_id, starte_date, but I'm not sure (I don't have MSSQL in front of me now to verify this). Commented Mar 7, 2013 at 17:07

2 Answers 2

4

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     |
+--------------+----------+------------+--------------+
0

The original query looks like an attempt to find the person in Group 146 who most recently began a leave. If that's the case, this should work:

SELECT TOP 1 person_id
FROM leaveperiod
WHERE group_id = 146
ORDER BY start_date DESC;
5
  • No, the original query returns several rows. I trust the application to work ok with Sybase, so the result must return several person_id's Commented Mar 7, 2013 at 16:58
  • Ah yes, there could be multiple people sharing the honor of having started leave most recently of those in Group 146.
    – mdoyle
    Commented Mar 7, 2013 at 17:19
  • I don't think these are the correct semantics anyway. It seems you assume that the Sybase behaviour is akin to WHERE start_date = MAX(start_date) OVER () but it seems from the docs to be more like WHERE start_date = MAX(start_date) OVER (PARTITION BY person_id) Commented Mar 8, 2013 at 10:52
  • I believe you are correct. I jumped to the conclusion that the first was correct because that way the query makes sense. If the behavior really is MAX(start_date) OVER (PARTITION BY person_id) then the original query seems to be just a tortuous way of getting DISTINCT person_id for that group in the table.
    – mdoyle
    Commented Mar 8, 2013 at 13:38
  • @mdoyle - But excluding those ones for which there doesn't exist a non null start_date and including multiple rows for a person_id if there are multiple rows matching the MAX(start_date) (though there may be constraints that mean neither of these are possible) Commented Mar 8, 2013 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.