SQL in ASP.NET - Order By sorting only one type incorrectly

Question

I'm working on an ASP.NET application which is supposed to output detector data for a chosen interstate, sorted by mile marker. We determined the best way to do this would be sorting it by longitude (West and East) or latitude(North and South) depending on which direction it goes. Here's the query that populates it.

SELECT [ShortWebID], [AvgSpeed], [update_time], [WebName] FROM [vwAverageSpeed] 

WHERE (([WebName] LIKE '%' + @WebName + '%') AND ([update_time] > @update_time)) 

ORDER BY CASE @WebName WHEN '%EB%'THEN [Longitude] WHEN '%WB%' THEN [Longitude] WHEN '%NB%' THEN [Latitude] WHEN '%SB%' THEN [Latitude] END

The WebName view has different listings such as 'I-64 WB at MP 3.1'. The ListItems they have access to are listed just as 'I-64'.

There's 4 different interstates, and it's working fine for everything but one, I-55/70. When that option is chosen, the list is not sorted by the latitude or longitude, but instead just by the order that they're in the table. I-55/70 is the only one with a / in it. Would this affect things?

Thank you in advance.

Edit: These are listed in the list item as 'I-55/70 WB' and so on in the same style as the example above. Sorry about the confusion.

Edit2: That did the trick. Thanks!

Answer 1

I-55/70 corresponds to neither of '%EB%, '%WB%', '%NB%' or '%SB%'.

Your ORDER BY clause is always NULL in that case.

What field do you want to sort on when the WebName is I-55/70?

Answer 2

It seems to me your ORDER BY clause should not work in any cases because "CASE @WebName WHEN '%EB%'" etc. should not evaluate to true. The case statement as you are using it does an equivalence comparison but the wildcard operators ("%") are only used for LIKE. Try this instead:

ORDER BY CASE 
WHEN @WebName LIKE '%EB%' THEN [Longitude] 
WHEN @WebName LIKE '%WB%' THEN [Longitude] 
WHEN @WebName LIKE '%NB%' THEN [Latitude] 
WHEN @WebName LIKE '%SB%' THEN [Latitude] 
END

The slash should not affect anything.