CODEWITHSUNDEEP
sql-server
Villie
Villie

Reputation: 281

SQL Server: result with given order

The below is the table structure:-

Table name: Records
---------------------------------------------------------------
| office ID| transactionName | transactionAmount | Date | uid |
---------------------------------------------------------------

I am trying to fetch results from the above table in a given order office id 2, 4, 1, 3. How can we specify the id's in ORDER BY clause so that the order of the output comes as specified.

Upvotes: 0

Views: 52

Answers (3)

Isaac
Isaac

Reputation: 3363

Here is another alternative...

IF OBJECT_ID('tempdb..#TempTable', 'U') IS NOT NULL DROP TABLE #TempTable

create table #TempTable
(
  OfficeID int
)

insert into #TempTable (OfficeID) values (1)
insert into #TempTable (OfficeID) values (2)
insert into #TempTable (OfficeID) values (3)
insert into #TempTable (OfficeID) values (4)

declare @CustomSortOrder varchar(100)

set @CustomSortOrder = '2~4~1~3~'

select * from #TempTable

select *
from #TempTable
order by charindex(convert(char(1), #TempTable.OfficeID) + '~', @CustomSortOrder)

Upvotes: 0

Sujeet Sinha
Sujeet Sinha

Reputation: 2433

You can use the following query which is primarily defining a custom sort order:

SELECT * FROM records
ORDER BY CASE officeID
    WHEN 2 THEN 1 
    WHEN 4 THEN 2 
    WHEN 1 THEN 3 
    WHEN 3 THEN 4
    ELSE 5
   END

Upvotes: 3

Kramb
Kramb

Reputation: 1092

Give it a custom sort order

SELECT [office ID], transactionName, transactionAmount, Date, uid
FROM Records
ORDER BY CASE WHEN [office ID] = '2' THEN '1'
              WHEN [office ID] = '4' THEN '2'
              WHEN [office ID] = '1' THEN '3'
              WHEN [office ID] = '3' THEN '4'

Upvotes: 1

Related Questions