CODEWITHSUNDEEP
sqlsql-server
Hasan Kaan TURAN
Hasan Kaan TURAN

Reputation: 361

SQL - Using specific 'order by' command

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..

Upvotes: 1

Views: 62

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

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.

Upvotes: 2

Related Questions