Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Aaron Bertrand Mar 7 '13 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. –  carlo.borreo Mar 7 '13 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. –  Martin Smith Mar 7 '13 at 16:15
    
That was my impression too. –  carlo.borreo Mar 7 '13 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). –  FrustratedWithFormsDesigner Mar 7 '13 at 17:07
add comment

2 Answers

up vote 4 down vote accepted

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     |
+--------------+----------+------------+--------------+
share|improve this answer
add comment

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;
share|improve this answer
    
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 –  carlo.borreo Mar 7 '13 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 Mar 7 '13 at 17:19
    
Can't you just change SELECT TOP (1) person_id to SELECT TOP (1) WITH TIES person_id...? –  Paul White Mar 8 '13 at 8:23
    
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) –  Martin Smith Mar 8 '13 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 Mar 8 '13 at 13:38
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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