{"action":"opened","issue":{"url":"https://api.github.com/repos/ProfilesRNS/ProfilesRNS/issues/158","repository_url":"https://api.github.com/repos/ProfilesRNS/ProfilesRNS","labels_url":"https://api.github.com/repos/ProfilesRNS/ProfilesRNS/issues/158/labels{/name}","comments_url":"https://api.github.com/repos/ProfilesRNS/ProfilesRNS/issues/158/comments","events_url":"https://api.github.com/repos/ProfilesRNS/ProfilesRNS/issues/158/events","html_url":"https://github.com/ProfilesRNS/ProfilesRNS/issues/158","id":321672861,"number":158,"title":"Order by in [Profile.Data].[Person.GetFacultyRanks]","user":{"login":"nicholaswilliambrown","id":2432061,"avatar_url":"https://avatars1.githubusercontent.com/u/2432061?v=4","gravatar_id":"","url":"https://api.github.com/users/nicholaswilliambrown","html_url":"https://github.com/nicholaswilliambrown","followers_url":"https://api.github.com/users/nicholaswilliambrown/followers","following_url":"https://api.github.com/users/nicholaswilliambrown/following{/other_user}","gists_url":"https://api.github.com/users/nicholaswilliambrown/gists{/gist_id}","starred_url":"https://api.github.com/users/nicholaswilliambrown/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nicholaswilliambrown/subscriptions","organizations_url":"https://api.github.com/users/nicholaswilliambrown/orgs","repos_url":"https://api.github.com/users/nicholaswilliambrown/repos","events_url":"https://api.github.com/users/nicholaswilliambrown/events{/privacy}","received_events_url":"https://api.github.com/users/nicholaswilliambrown/received_events","type":"User","site_admin":false},"labels":[{"id":19833914,"url":"https://api.github.com/repos/ProfilesRNS/ProfilesRNS/labels/bug","name":"bug","color":"fc2929","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/ProfilesRNS/ProfilesRNS/milestones/9","html_url":"https://github.com/ProfilesRNS/ProfilesRNS/milestone/9","labels_url":"https://api.github.com/repos/ProfilesRNS/ProfilesRNS/milestones/9/labels","id":3333934,"number":9,"title":"2.12.0","description":"","creator":{"login":"nicholaswilliambrown","id":2432061,"avatar_url":"https://avatars1.githubusercontent.com/u/2432061?v=4","gravatar_id":"","url":"https://api.github.com/users/nicholaswilliambrown","html_url":"https://github.com/nicholaswilliambrown","followers_url":"https://api.github.com/users/nicholaswilliambrown/followers","following_url":"https://api.github.com/users/nicholaswilliambrown/following{/other_user}","gists_url":"https://api.github.com/users/nicholaswilliambrown/gists{/gist_id}","starred_url":"https://api.github.com/users/nicholaswilliambrown/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nicholaswilliambrown/subscriptions","organizations_url":"https://api.github.com/users/nicholaswilliambrown/orgs","repos_url":"https://api.github.com/users/nicholaswilliambrown/repos","events_url":"https://api.github.com/users/nicholaswilliambrown/events{/privacy}","received_events_url":"https://api.github.com/users/nicholaswilliambrown/received_events","type":"User","site_admin":false},"open_issues":5,"closed_issues":0,"state":"open","created_at":"2018-05-09T17:48:38Z","updated_at":"2018-05-09T18:14:47Z","due_on":"2018-06-05T07:00:00Z","closed_at":null},"comments":0,"created_at":"2018-05-09T18:14:47Z","updated_at":"2018-05-09T18:14:47Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"If you change the sort order it can sometimes mess up the results. It’s interesting. In the SP I would see the correct order when I just ran the inner part of the select:\r\n                                                                SELECT CAST(MAX(FacultyRankID) AS VARCHAR(50)) FacultyRankID,\r\n                                                                                                LTRIM(RTRIM(FacultyRank)) FacultyRank, FacultyRankSort                                                           \r\n                                                                FROM [Profile.Data].[Person.FacultyRank] WITH (NOLOCK) where facultyrank <> ''                                                         \r\n                                                                group by FacultyRank ,FacultyRankSort \r\n\r\nHowever, if you ran the whole thing it would reorder the results once the LEFT OUTER JOIN’s were applied. Note that we did have some imports where the same FacultyRank entries had different FacultyRankSort entries so that may have corrupted things.\r\n\r\nAnyway, adding the ORDER BY FacultyRankSort at the end of the SELECT fixed it for us, see below.\r\nEric\r\n\r\nSET ANSI_NULLS ON\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\nALTER procedure [Profile.Data].[Person.GetFacultyRanks]\r\n                -- Add the parameters for the stored procedure here\r\n\r\nAS\r\nBEGIN\r\n                -- SET NOCOUNT ON added to prevent extra result sets from\r\n                -- interfering with SELECT statements.\r\n                SET NOCOUNT ON;\r\n\r\n                SELECT x.FacultyRankID, x.FacultyRank,  n.NodeID, n.Value URI\r\n                                FROM (\r\n                                                                SELECT CAST(MAX(FacultyRankID) AS VARCHAR(50)) FacultyRankID,\r\n                                                                                                LTRIM(RTRIM(FacultyRank)) FacultyRank, FacultyRankSort                                                           \r\n                                                                FROM [Profile.Data].[Person.FacultyRank] WITH (NOLOCK) where facultyrank <> ''                                                         \r\n                                                                group by FacultyRank ,FacultyRankSort \r\n                                                ) x \r\n                                                LEFT OUTER JOIN [RDF.Stage].InternalNodeMap m WITH (NOLOCK)\r\n                                                                ON m.class = 'http://profiles.catalyst.harvard.edu/ontology/prns#FacultyRank'\r\n                                                                                AND m.InternalType = 'FacultyRank'\r\n                                                                                AND m.InternalID = CAST(x.FacultyRankID AS VARCHAR(50))\r\n                                                LEFT OUTER JOIN [RDF.].Node n WITH (NOLOCK)\r\n                                                                ON m.NodeID = n.NodeID\r\n                                                                                AND n.ViewSecurityGroup = -1\r\n                                ORDER BY FacultyRankSort\r\n"}}
{"actor":{"display_login":"nicholaswilliambrown"}}