March 2, 2015 at 9:22 am
One member table store member ID like this format for the same member:
(not all member has 4 ID, some has 2 some may have 3...)
12345P (1)
12345H (2)
12345S (3)
12345 (4)
I use select statement as below.
Select memberID from member order by ...
How to complete code so that can order by depending on last of letter? (P=1, H=2, S=3, no letter = 4)
March 2, 2015 at 9:26 am
adonetok (3/2/2015)
One member table store member ID like this format for the same member:(not all member has 4 ID, some has 2 some may have 3...)
12345P (1)
12345H (2)
12345S (3)
12345 (4)
I use select statement as below.
Select memberID from member order by ...
How to complete code so that can order by depending on last of letter? (P=1, H=2, S=3, no letter = 4)
ORDER BY CASE WHEN RIGHT(memberID, 1) = 'P' THEN 1 WHEN RIGHT(memberID, 1) = 'H' THEN 2 WHEN RIGHT(memberID, 1) = 'S' THEN 3 ELSE 4 END
March 2, 2015 at 9:38 am
Thank you. It works.
March 2, 2015 at 9:49 am
This is going to be slow.
You might gain speed with an additional order column. It could be a persisted computed column.
March 2, 2015 at 10:04 am
Luis Cazares (3/2/2015)
This is going to be slow.You might gain speed with an additional order column. It could be a persisted computed column.
+1
that way you can include it in indexes etc...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 2, 2015 at 10:30 am
Of course, if you're returning just 4 rows it won't make a difference. 🙂
As with most things in SQL, it depends.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply