CODEWITHSUNDEEP
sqlsql-servert-sql
thunderbird
thunderbird

Reputation: 2733

Sort columns based on the order inside SQL in function

i have a query like the one below and i want records to be returned in the same order as the id's specified inside the 'in' function.

SELECT * FROM table 1 where id in(12,6,4,3,13)

Can i do this using sql alone or do i need to write my own sorting function.

Upvotes: 2

Views: 1302

Answers (2)

dantibb
dantibb

Reputation: 115

create a table with your ID's, then join to table, ordering by another identity column.

Presumably if you have 10k id's they will not be manually entered, so prehaps able to build sort/join table in a different way. SHould also be more efficient than using large in

create table #tempID(idSort int identity(1,1), id int)
insert into #tempID(id)
select 12 union all
select 6 union all
select 4 union all
select 3 union all
select 13 

select * from table t1
    inner join #tempID t2
        on t1.id = t2.id
order by t2.idSort

To create sort table dynamically, you need this function (or similar):

create FUNCTION [dbo].[comma_sep_var_intSort] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (idSort int identity(1,1), id int NOT NULL 
) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (id)
         VALUES (cast(substring(@list, @pos + 1, @valuelen) as int))
      SELECT @pos = @nextpos
   END
  RETURN
END

Then join like this:

declare @idList varchar(max)

set @idLIst = '12,6,4,3,13'

    select * from table t1
        inner join [dbo].[comma_sep_var_int](@idList) t2
            on t1.id = t2.id
    order by t2.idSort

Upvotes: 3

John Woo
John Woo

Reputation: 263723

you can use CASE to custom sort the records,

ORDER BY CASE WHEN ID = 12 THEN 1
              WHEN ID = 6 THEN 2
              WHEN ID = 4 THEN 3
              WHEN ID = 3 THEN 4
              WHEN ID = 13 THEN 5
              ELSE 6
         END, ID

Upvotes: 2

Related Questions