CODEWITHSUNDEEP
sqlsql-server
Jankya
Jankya

Reputation: 958

SQL Query returns Invalid Sequence

Hi I am Using Simple Query with IN Operator. I am using following Query

SELECT CustID  FROM Customer WHERE CustID in (6,34,5,4,3,2,1)

Now it displays Following Order

custId  
1   
2   
3   
4   
5   
6   
34

But I want the result as the same sequence which I pass in the IN Clause.

Upvotes: 3

Views: 721

Answers (6)

Kishor Naik
Kishor Naik

Reputation: 91

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,')

Upvotes: 2

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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]

Upvotes: 0

Devart
Devart

Reputation: 122002

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

Upvotes: 0

TGH
TGH

Reputation: 39268

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

Upvotes: 3

Borniet
Borniet

Reputation: 3546

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

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881913

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.

Upvotes: 0

Related Questions