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'm getting a syntax error in SQL Server Management Studio (SQL Server 2008) for the following query:

SELECT 
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM 
    NameAddressCoverHoldingTable 
GROUP BY
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, '')))  Address4,
    PostCode,
    PolicyId
ORDER BY
    PolicyId, 
    MIN(EffectiveOn);

Error message: Msg 102, Level 15, State 1, Line 15 Incorrect syntax near 'Address4'

I can't see what is wrong - this works:

SELECT 
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    --RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM 
    NameAddressCoverHoldingTable 
GROUP BY
    Address1,
    Address2,
    Address3,
    --RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
ORDER BY
    PolicyId, 
    MIN(EffectiveOn);

What is the syntax error?

share|improve this question
2  
Hi Pete, basic syntax questions are off-topic here on DBA.SE. If this were not already self answered I would move it to Stack Overflow for you, but I think if I migrate it now it would be closed as "Too Localized" there. –  JNK Dec 6 '12 at 14:13
add comment

closed as off topic by JNK Dec 6 '12 at 14:13

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

Figured it out shortly after I posted. I need to remove the alias from the GROUP BY clause.

Solution:

SELECT 
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM 
    NameAddressCoverHoldingTable 
GROUP BY
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))),
    PostCode,
    PolicyId
ORDER BY
    PolicyId, 
    MIN(EffectiveOn);
share|improve this answer
    
Correct. You can use aliases in ORDER BY though, if you want: ORDER BY PolicyId, EffectiveOn; –  ypercube Dec 6 '12 at 14:18
add comment

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