SQL - Ordering with part of string

Question

Sorry for repost. But I need. I have 8 length string and my query;

select distinct Siparisler.IsEmriNo, MakinelerVeParcalar.Ozellik, DurumBilgisi.ID 
from Siparisler,
     MakinelerVeParcalar,
     DurumBilgisi 
where DurumBilgisi.ID = MakinelerVeParcalar.ID
  and Siparisler.IsEmriNo = MakinelerVeParcalar.IsEmriNo

and my IsEmriNo list;

MR18U001
MR18U002
MR18V001
MR19U003
MR19V002
MR19V001
OC19U002
OC18V005
OC18U007
SB18U010
SB18V005
SB19U028

And I want

MR19U003
MR19V002
MR19V001
SB19U028
OC19U002
MR18U002
MR18U001
MR18V001
SB18U010
SB18V005
OC18U007
OC18V005

First ordering: 3rd and 4th character (Descending 19,18,17 ...)

Second ordering: 1st and 2nd character (Ascending MR,SB,OC ... -> specific for me)

Third ordering:
Last 3 character (Descending 030,029,028 ... 001)

Not duplicate with my old post.

Is it possible?

Possible duplicate of SQL - Using specific 'order by' command — Ilyes
– Ilyes, Commented Jun 7, 2019 at 12:16
Tip of today: Switch to modern, explicit JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. ( — jarlh
– jarlh, Commented Jun 7, 2019 at 12:21

Arulkumar · Accepted Answer · 2019-06-07 12:23:06Z

1

The following order by will work in your scenario:

order by substring(IsEmriNo, 3, 2) desc,
         CASE WHEN IsEmriNo LIKE '%MR%' THEN 0
              WHEN IsEmriNo LIKE '%SB%' THEN 1
              WHEN IsEmriNo LIKE '%OC%' THEN 2
             END,
         right(IsEmriNo, 3) desc

answered Jun 7, 2019 at 12:23

Arulkumar

13.3k15 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - Ordering with part of string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related