Post reply

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

  • May 8, 2007 at 2:28 pm

    #118452

    What the hell am I missing, I've combed over this!

    SELECT DISTINCT

                         a.[FirstName] + ' ' + a.[LastName] AS FullName,

                            s.[Phone] AS Phone,

                            s.[HomePhone] AS HomePhone,

                            a.[PersonalEmail] AS Email,

                            s.[tyUserID],

                            s.[Email] AS AlternateEmail,

                            s.[Addr1] +  ' ' + s.[Addr2] + ' ' + s.[City] + ',' + s.[State] + ' ' + s.[Zip] + ' ' + dbo.bbGetCountryDescription(s.[syCountryId]) AS PrimaryAddress,

                            a.[PrefAddress1] + ' ' + a.[PrefAddress2] + ' ' + a.[PrefCity] + ' ' + a.[PrefState] + ' ' + a.[PrefZip] + ' ' + dbo.bbGetCountryDescription([PrefadCountryId]) AS ShippingAddress,

                                             u.ttUserID                                     

                                             FROM  [bbIBM] a

                            INNER JOIN c2000.dbo.[tyUser] s ON s.[tyUserId] = a.[tyUserId]

                            LEFT JOIN TtUsers u ON u.tyUserId = s.tyUserID

                            LEFT JOIN ttUserRoles ur ON ur.ttUserId = u.ttUserId AND ur.ttRoleId = @xttRoleID

     INNER JOIN AdClassSchedTerm st ON st.AdTermID = @xTermID AND st.UserID = u.ttUserID  WHERE   1 = 1

                      AND s.[Active] = 1

                      AND u.[Active] = 1

                      AND u.[syCampusID] = @xsyCampusID

     ORDER BY a.[FirstName], a.[LastName], s.[Phone], s.[HomePhone], a.[PersonalEmail], s.[tyUserID], s.[Email], s.[Addr1], s.[Addr2], s.[City], s.[State], s.[Zip], a.[PrefAddress1], a.[PrefAddress2], a.[PrefCity], a.[PrefState], a.[PrefZip], u.ttUserID ASC

    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

  • May 8, 2007 at 2:35 pm

    #705070

    Something like this should do it.

    ORDER BY FullName, s.[Phone], s.[HomePhone], a.[PersonalEmail], s.[tyUserID], s.[Email], ShippingAddress, u.ttUserID ASC

  • May 8, 2007 at 2:39 pm

    #705073

    that works but problem is, then I lose control of whether I want to order FirstName before LastName or vice versa precedence

  • May 8, 2007 at 2:41 pm

    #705074

    Then you'll need to return the first Name, and Last name individually and exclude the "FullName" value. and then Concantenate on the presentation Layer.

     

  • May 8, 2007 at 2:42 pm

    #705077

    actually no I don't it works fine...thanks.  So you can use aliases and in fact looks like you must include the Alias, not the individual fields that make up the Alias in your ORDER BY

  • May 8, 2007 at 2:58 pm

    #705086

    It's not that you must.  I think you came on one of the rare exceptions.

  • May 8, 2007 at 3:06 pm

    #705088

    You can use the alias, the calculation firstname + lastname, or the column number...

    so...

    order by 'FullName'

    order by firstname + ' ' + last name

    order by 1

    all produce the same result...

    Cheers,

    Ben

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • May 8, 2007 at 3:19 pm

    #705093

    Thanks all!

  • August 19, 2009 at 10:45 am

    #1041716

    I know there have been plenty of good replies already, but when I have a lot of concatenated columns or aliased columns the easiest way for me to handle the order by clause is to use the column numbers instead of the field names. Just be careful if you ever delete fields to update the order by. So in this case you would just use Order by 1,2,3,4,5,6, etc.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply