SQL - Using specific 'order by' command

Question

My query;

select *
from
(
    Select distinct Siparisler.IsEmriNo,MakinelerVeParcalar.Ozellik,DurumBilgisi.ID
    from Siparisler,MakinelerVeParcalar,DurumBilgisi Where DurumBilgisi.ID = MakinelerVeParcalar.ID and Siparisler.IsEmriNo = MakinelerVeParcalar.IsEmriNo
) x
order by
    case when IsEmriNo LIKE '%OC%' then 0
         when IsEmriNo LIKE '%SB%' then 1
         when IsEmriNo LIKE '%MR%' then 2 end,
    IsEmriNo

My output;

IsEmriNo    Ozellik    ID
--------    -------    --
OC19U001    Ozel       1
OC19U002    Yok        2
OC19U003    Yok        3
SB19U001    Yok        4
SB19U002    Yok        5
MR19U001    Yok        6
MR19U002    Yok        7
MR19U003    Yok        8

But I want;

IsEmriNo    Ozellik    ID
--------    -------    --
OC19U003    Yok        3
OC19U002    Yok        2
OC19U001    Ozel       1
SB19U003    Yok        5
SB19U002    Yok        4
MR19U003    Yok        8
MR19U002    Yok        7
MR19U001    Yok        6

I want ordering with last 3 char and ordering first 2 char with 'case' command.I cant use left-right command with case.Please help me... Thank you..

Gordon Linoff · Accepted Answer · 2019-06-07 11:01:58Z

2

Hmmm. If I understand correctly, you want to order by the minimum id based on the first five characters (or perhaps the first two -- it is not clear). Then by the id descending.

You can do this using window functions:

order by min(id) over (partition by left(IsEmriNo, 5)),
         id desc

If you want to order by the last two characters in IsEmriNo:

order by min(id) over (partition by left(IsEmriNo, 5)),
         right(IsEmriNo, 2) desc

edited Jun 7, 2019 at 11:01

answered Jun 7, 2019 at 10:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Hasan Kaan TURAN Over a year ago

ID is not important.It is random.I need last 3 char for descending

Gordon Linoff Over a year ago

@HasanKaanTURAN . . . I adjusted the answer.

Hasan Kaan TURAN Over a year ago

I have MR19V003. But ordering; MR19U003,MR19U002,MR19U001,MR19V003... Why ? I want ; MR19U003,MR19V003,MR19U002,MR19U001

Tim Biegeleisen · Accepted Answer · 2019-06-07 10:37:42Z

2

I would suggest that you just order descending on the second level of your current sort, keeping the rest the same:

ORDER BY
    CASE WHEN IsEmriNo LIKE '%OC%' THEN 0
         WHEN IsEmriNo LIKE '%SB%' THEN 1
         WHEN IsEmriNo LIKE '%MR%' THEN 2 END,
    IsEmriNo DESC;

You might in fact want to just id DESC instead, but perhaps it is coincidence that the id and IsEmriNo columns happen to order the same way.

answered Jun 7, 2019 at 10:37

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

Hasan Kaan TURAN Over a year ago

ID is random.Not important for me

Arulkumar · Accepted Answer · 2019-06-07 10:38:34Z

0

Using SUBSTRING() with PATINDEX() in ORDER BY will help:

SELECT IsEmriNo, Ozellik, ID
FROM TableName 
ORDER BY SUBSTRING(IsEmriNo, 1, PATINDEX('%[0-9]%', IsEmriNo) - 1) DESC, 
         SUBSTRING(IsEmriNo, PATINDEX('%[0-9]%', IsEmriNo), LEN(IsEmriNo)) ASC

answered Jun 7, 2019 at 10:38

Arulkumar

13.3k15 gold badges51 silver badges76 bronze badges

1 Comment

Hasan Kaan TURAN Over a year ago

Invalid length parameter passed to the LEFT or SUBSTRING function.

Collectives™ on Stack Overflow

SQL - Using specific 'order by' command

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related