SQL Query returns Invalid Sequence

SELECT CustID  FROM Customer WHERE CustID in (6,34,5,4,3,2,1)
ORDER BY  CASE WHEN CustId = 34
               THEN 5.5
               ELSE CustId
          END DESC

Try this Query Ramdas......

SELECT CustID  FROM Customer WHERE CustID in (6,34,5,4,3,2,1) 
ORDER BY CHARINDEX(','+CAST(CustID as varchar(MAX))+',', ',6,34,5,4,3,2,1,')

Standard SQL doesn't specify default orders in either select statements or in clauses. The rows will be returned in whatever order the DBMS wants to.

If you want to force the order to something specific, you need to use an order-by clause.

Some SQL implementations may allow this to be configured, but not any of the ones I use regularly. Worst case, you can try something like:

select 1 as x, custid from customer where custid = 6
union all select 2 as x, custid from customer where custid = 34
union all select 3 as x, custid from customer where custid = 5
order by x

but it's pretty ugly and not overly efficient.

You can't, if you want that to happen, you will need a new field like 'showorder' or something, on which you can order your result:

SELECT CustID FROM Customer WHERE CustID IN (6,34,5,4,3,2,1) ORDER BY showorder

Try this one -

DECLARE @id TABLE
(
      i INT IDENTITY(1,1)
    , id INT
)

INSERT INTO @id (id)
VALUES (6),(34),(5),(4),(3),(2),(1)

SELECT c.CustID  
FROM dbo.Customer c
JOIN @id i ON i.id = c.CustID
ORDER BY i.i

Use a split string CTE with ordered values. Hence you will be able to sort any of dataset

DECLARE @test nvarchar(100) = '6,34,5,4,3,2,1';
;WITH cte AS
 (
  SELECT SUBSTRING(@test , 0, CHARINDEX( ',', @test)) AS val ,
         CAST(STUFF (@test + ',' , 1, CHARINDEX( ',', @test), '') AS nvarchar(100 )) AS stval,
         1 AS [level]
  UNION ALL
  SELECT SUBSTRING(stval , 0, CHARINDEX( ',', stval)),
         CAST(STUFF (stval, 1, CHARINDEX(',' , stval ), '' ) AS nvarchar(100 )),
         [level] + 1
  FROM cte
  WHERE stval != ''
  )
  SELECT c2.CustID
  FROM dbo.Customer c2 JOIN cte c ON c2.CustID = c.val
  ORDER BY c.[level]

Demo on SQLFiddle

Also you can wrap CTE in function

CREATE FUNCTION dbo. SplitStrings_CTE(@List nvarchar( 1000), @Delimiter nvarchar(1 ))
RETURNS TABLE
AS
RETURN
WITH cte AS
 (
  SELECT SUBSTRING(@List , 0 , CHARINDEX (@Delimiter, @List)) AS val ,
         CAST(STUFF (@List + @Delimiter, 1 , CHARINDEX (@Delimiter, @List), '') AS nvarchar (1000)) AS stval, 1 AS [level]
  UNION ALL
  SELECT SUBSTRING(stval , 0 , CHARINDEX (@Delimiter, stval)),
         CAST(STUFF (stval , 1, CHARINDEX( @Delimiter , stval ), '' ) AS nvarchar(1000 )), [level] + 1
  FROM cte
  WHERE stval != ''
  )
  SELECT REPLACE(val , ' ' , '' ) AS val, [level]
  FROM cte

SELECT statement with using function

DECLARE @test nvarchar(100) = '6,34,5,4,3,2,1';
SELECT CustID
FROM dbo.Customer c2 JOIN (SELECT val, [level] FROM dbo.SplitStrings_CTE(@test, ',')
                           ) c ON c2.CustID = c.val
ORDER BY c.[level]

OR

DECLARE @test nvarchar(100) = '6,34,5,4,3,2,1';
SELECT CustID
FROM dbo.Customer c2 CROSS APPLY dbo.SplitStrings_CTE(@test, ',') c                           
WHERE c2.CustID = c.val
ORDER BY c.[level]