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

Question

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

dba321

Hall of Fame

Points: 3917
More actions
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.

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Something like this should do it.

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

dba321 Hall of Fame Points: 3917 More actions · Answer 2

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

Ray M SSC-Insane Points: 21093 More actions · Answer 3

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.

dba321 Hall of Fame Points: 3917 More actions · Answer 4

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 6

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...

dba321 Hall of Fame Points: 3917 More actions · Answer 7

dba321

Hall of Fame

Points: 3917

May 8, 2007 at 3:19 pm

#705093

Thanks all!

Sarah Wagner Hall of Fame Points: 3178 More actions · Answer 8

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.