SQL statement not sorting correctly

[DougP]

Select ROW_NUMBER() OVER 
(ORDER BY WAGM.SOWTracker,WAGM.Vendor ASC, 
WAGM.Manager ASC, WAGM.ResourceLastName ASC, WAGM.ResourceFirstName ASC) AS Seq,
WAGM.SOWTracker, WAGM.Vendor,
 WAGM.Manager, WAGM.ResourceLastName, 
 WAGM.ResourceFirstName,
 [highlight #C4A000] 'Timesheet' = Case  When WAG.ePrizeID IS NULL 
  Then 'NOT IN' Else 'IN'  End ,[/highlight]   WAG.TotalHours, 
   Convert(nvarchar(12),WAG.WeekEndDate,101) AS WeekEndDate,
   Convert(nvarchar(12),WAG.TimeSheetSubmittedDate,101) as DateSubmitted, 
   Convert(nvarchar(12),WAG.ApprovedDate,101) AS ApprovedDate
    From WeekAtAGlanceMissing WAGM left join 
    WeekAtAGlance WAG ON WAGM.ePrizeID = WAG.ePrizeID  
     Inner Join SOWResources SOWR ON 
     WAGM.ePrizeID = SOWR.ePrizeID 
     Where SOWR.EndDate = '2100-12-31' 
     And WAGM.Vendor is not null 
     order by [highlight #FCE94F]WAG.Timesheet ASC,[/highlight]      WAG.Vendor ASC, WAG.Manager ASC, 
     WAG.ResourceLastName ASC, 
WAG.ResourceFirstName ASC;

this is in a ASP.NET Grid view and when clicking on the Timesheet column the first time it sorts backwards.
There are only two possible values in that column 'NOT IN' or 'IN'. All other columns sort just fine the first time.
When clicking the NOT IN's show first then IN's. clicking it again sorts correctly, IN's followed by NOT IN's.

TIA

DougP

 

[gmmastros]

I'm guessing you have a column in the WAG table named timesheet. Your order by is referencing the table, so it will sort by the data that is in the table, not the data represented by the case statement.

If you remove the WAG. part from the order by, I suspect you'll get the ordering you want. However, this is confusing and not at all self explanatory, so I would actually suggest you put the case statement in the order by clause, like this.

Select ROW_NUMBER() OVER 
(ORDER BY WAGM.SOWTracker,WAGM.Vendor ASC, 
WAGM.Manager ASC, WAGM.ResourceLastName ASC, WAGM.ResourceFirstName ASC) AS Seq,
WAGM.SOWTracker, WAGM.Vendor,
 WAGM.Manager, WAGM.ResourceLastName, 
 WAGM.ResourceFirstName,
  'Timesheet' = Case  When WAG.ePrizeID IS NULL 
  Then 'NOT IN' Else 'IN'  End ,   WAG.TotalHours, 
   Convert(nvarchar(12),WAG.WeekEndDate,101) AS WeekEndDate,
   Convert(nvarchar(12),WAG.TimeSheetSubmittedDate,101) as DateSubmitted, 
   Convert(nvarchar(12),WAG.ApprovedDate,101) AS ApprovedDate
    From WeekAtAGlanceMissing WAGM left join 
    WeekAtAGlance WAG ON WAGM.ePrizeID = WAG.ePrizeID  
     Inner Join SOWResources SOWR ON 
     WAGM.ePrizeID = SOWR.ePrizeID 
     Where SOWR.EndDate = '2100-12-31' 
     And WAGM.Vendor is not null 
     order by [!]Case  When WAG.ePrizeID IS NULL 
  Then 'NOT IN' Else 'IN'  End[/!],      WAG.Vendor ASC, WAG.Manager ASC, 
     WAG.ResourceLastName ASC, 
WAG.ResourceFirstName ASC;

I prefer it this way because it is super obvious what the ordering bill be.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom