0

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?

  • 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
1

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);
  • Correct. You can use aliases in ORDER BY though, if you want: ORDER BY PolicyId, EffectiveOn; – ypercubeᵀᴹ Dec 6 '12 at 14:18

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