CODEWITHSUNDEEP
sqlsql-servert-sql
Shubhadeep Chattopadhyay
Shubhadeep Chattopadhyay

Reputation: 171

Display order of a SQL Query without order by clause

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 a same order which is given i.e; 10,9,5,7,3,8

Actually whatever I'll give the result will display with given order without order by ascending or descending.

How can I do that? Please help.

Upvotes: 7

Views: 982

Answers (7)

SQLDiver
SQLDiver

Reputation: 2018

The simplest way to achieve this is probably to use a table-valued constructor as a table expression containing the ids with a sort value, then order on this 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

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272036

You can use CHARINDEX function in an odd way: search for the id in the comma separated list and order the result by the position.

Consider this list for example 10,9,5,7,3,8... the substring 10 appears at 1st position while 9 appears at 4th. Just order by the 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

Upvotes: 2

Maciej Los
Maciej Los

Reputation: 8591

If Ids is a variable passed as 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

Upvotes: 0

Biswabid
Biswabid

Reputation: 1411

Following query will give you the exact result (without order by):

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)

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do this dynamically if your list is a comma-delimited-string. 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, t.N, 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 list of empIds as csv string and use the splitter:

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

Upvotes: 1

Jasqlg
Jasqlg

Reputation: 183

You can use a table variable to pass the inputs. You must insert the 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 this.

Upvotes: 7

rabudde
rabudde

Reputation: 7722

No way to do this natively. 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;

Upvotes: 13

Related Questions