SQL query display order without a clause

Question

I am writing a simple query in SQL Server:

Select EmpId, EmpName, Sal from Emp where EmpId in (10,9,5,7,3,8); 

I want to get the output in the same order as given by ie; 10,9,5,7,3,8

In fact, everything that I give the result will be displayed in the specified order without ascending or descending.

How can i do this? Please, help.

Answer 1

It is impossible to do this initially. Try:

 SELECT EmpId,EmpName,Sal FROM Emp WHERE EmpId IN (10,9,5,7,3,8) ORDER BY CASE EmpId WHEN 10 THEN 1 WHEN 9 THEN 2 WHEN 5 THEN 3 WHEN 7 THEN 4 WHEN 3 THEN 5 WHEN 8 THEN 6 ELSE 7 END; 

Answer 2

You can use a table variable to pass input. You must insert records into this table variable in the desired order.

 Declare @empids table(id int identity(1,1),empid int) insert into @empids values(10),(9),(5),(7),(3),(8) Select e.EmpId,e.empname,e.sal from Emp e join @empids t on e.EmpId = t.empid order by t.id 

Try it.

Answer 3

You can use CHARINDEX an odd way: look for the identifier in a comma-separated list and sort the result by position,

Consider this list, for example, 10,9,5,7,3,8 ... substring 10 appears at position 1 ^st and 9 appears at 4 ^th . Just order a substring position.

 CREATE TABLE Emp (EmpId int, EmpName varchar(100), Sal int) ; INSERT INTO Emp (EmpId, EmpName, Sal) VALUES (1, 'John', NULL), (2, 'Jane', NULL), (3, 'Smith', NULL), (4, 'Doe', NULL), (5, 'Ben', NULL), (6, 'Steve', NULL), (7, 'Andrew', NULL), (8, 'Simon', NULL), (9, 'Jack', NULL), (10, 'Allen', NULL) ; SELECT EmpId, EmpName, Sal FROM Emp WHERE EmpId in (10,9,5,7,3,8) ORDER BY CHARINDEX(CONCAT(',', EmpId, ','), CONCAT(',', '10,9,5,7,3,8', ',')) ; 

Result:

 EmpId | EmpName | Sal ------+---------+----- 10 | Allen | NULL 9 | Jack | NULL 5 | Ben | NULL 7 | Andrew | NULL 3 | Smith | NULL 8 | Simon | NULL 

Answer 4

You can do this dynamically if your list is a comma separator. First, you must have a splitter function. Here is the DelimitedSplit8k written by Jeff Moden:

 CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) ,E2(N) AS (SELECT 1 FROM E1 a, E1 b) ,E4(N) AS (SELECT 1 FROM E2 a, E2 b) ,cteTally(N) AS( SELECT TOP (ISNULL(DATALENGTH(@pString), 0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ) ,cteStart(N1) AS( SELECT 1 UNION ALL SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString, tN, 1) = @pDelimiter ), cteLen(N1, L1) AS( SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1),0) - s.N1, 8000) FROM cteStart s ) SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pString, l.N1, l.L1) FROM cteLen l 

Then you declare the empId list as a csv string and use the delimiter:

 DECLARE @empIds VARCHAR(MAX) = '10,9,5,7,3,8'; SELECT e.EmpId, e.EmpName, e.Sal FROM Emp e INNER JOIN dbo.DelimitedSplit8K(@empIds, ',') s ON s.Item = l.EmpId ORDER BY s.ItemNumber 

Answer 5

The following query will give you the exact result (no order):

 SELECT EmpId,EmpName,Sal FROM Emp WHERE EmpId IN (10) union all SELECT EmpId,EmpName,Sal FROM Emp WHERE EmpId IN (9) union all SELECT EmpId,EmpName,Sal FROM Emp WHERE EmpId IN (5) union all SELECT EmpId,EmpName,Sal FROM Emp WHERE EmpId IN (7) union all SELECT EmpId,EmpName,Sal FROM Emp WHERE EmpId IN (3) union all SELECT EmpId,EmpName,Sal FROM Emp WHERE EmpId IN (8) 

Answer 6

If Ids is a variable passed as an input parameter to a stored procedure, you can split it using CTE .

 --@inputIDs VARCHAR(300) => '10,9,5,7,3,8' ;WITH MyIds AS ( SELECT 1 AS Position, CONVERT(INT, LEFT(@inputIds, CHARINDEX(',', @inputIDs)-1)) AS MyId, RIGHT(@inputIds, LEN(@inputIds) - CHARINDEX(',', @inputIDs)) AS Remainder WHERE CHARINDEX(',', @inputIDs)>0 UNION ALL SELECT Position +1 AS Position, CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS MyId, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder FROM MyIds WHERE CHARINDEX(',', Remainder)>0 UNION ALL SELECT Position +1 AS Position, CONVERT(INT, Remainder) AS MyId, NULL AS Remainder FROM MyIds WHERE CHARINDEX(',', Remainder)=0 ) SELECT e.EmpId, e.EmpName, e.Sal FROM Emp AS e INNER JOIN MyIds AS a ON e.EmpId = a.MyId ORDER BY a.Position 

Answer 7

The simplest way to achieve this is probably to use a constructor with a table value in the form of a table expression containing identifiers with a sort value, then order by that value:

 Select Emp.EmpId, Emp.EmpName, Emp.Sal from Emp Inner Join ( Values (10, 1), (9, 2), (5, 3), (7, 4), (3, 5), (8, 6) ) EmpIds (EmpId, Sort) On Emp.EmpId = EmpIds.EmpId Order By EmpIds.Sort Asc