Precise sort order of records that were found in a IN split function in SQL Server

Question

I need to return records that I send to a stored procedure in a comma-separated string - like this:

@PMID = 29573145,24106086,20513766,24326307

I have a stored procedure that pulls records such as

SELECT 
    data, 
    PMID  
FROM
    [dbo].[ADMIN_Publication_JSON] 
WHERE 
    PMID IN (SELECT DATA FROM dbo.Split(@PMID, ','))

The problem that I am having is that the return record set is random and I need it precise because my end user could change the order and the records need to be displayed in that order which would change the order in the comma string. Is this possible or do I need to totally change the way I pull the data? Thanks

Currently 2016 but I have access to 2012 also – Bill Nov 10 '18 at 20:45 — Bill, Nov 10 '18 at 20:45

Ilyes · Accepted Answer · 2019-01-05T10:15:05.173

5

You can use a window function like

Select T1.data, 
       T1.PMID  
FROM [dbo].[ADMIN_Publication_JSON] T1 INNER JOIN
     (SELECT Data,
             ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN
      FROM dbo.Split(@PMID,',')
     ) T2 ON  T1.PMID = T2.Data 
ORDER BY T2.RN;

Here is a little sample:

CREATE TABLE T(
  ID INT,
  SomeValue VARCHAR(45)
);

INSERT INTO T VALUES
(1, 'One'),
(2, 'Two'),
(3, 'Three'),
(4, 'Four'),
(5, 'Five');

DECLARE @IDs VARCHAR(200) = '3,5,2';

SELECT T.*
FROM T INNER JOIN 
       (SELECT Value,
               ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) Seq
       FROM STRING_SPLIT(@Ids, ',') --instead of your function
       ) TT
       ON T.ID = TT.Value
ORDER BY TT.Seq;

Live Demo

edited Jan 05 '19 at 10:15

answered Nov 10 '18 at 20:59

Ilyes

14,640
4
29
55

How can I use that with a [FN_ListToTable] ? – Bill Nov 10 '18 at 21:16
https://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function – Bill Nov 10 '18 at 21:17
I think I post the first query for you @user1314159 and add extra samples to make it clear. – Ilyes Nov 10 '18 at 21:19
Fantastic-- Thanks – Bill Nov 10 '18 at 21:24
I dont think we need `ROW_NUMBER()` – Nikhil Vartak Nov 10 '18 at 21:30
Alright, then what we need @NikhilVartak? Please feel free to post an answer without using `ROW_NUMBER`. – Ilyes Nov 10 '18 at 21:31
I have added answer. Let me know if it does not work. – Nikhil Vartak Nov 10 '18 at 21:36
This is a bad solution. `ROW_NUMBER() OVER(ORDER BY (SELECT NULL))` will generate numbers in an **arbitrary** order. It might be the same order as the order of the items in the comma delimited string but it might also be a different order. – Zohar Peled Nov 11 '18 at 06:07

score 0 · Answer 2 · answered Nov 10 '18 at 21:34

0

Split method does not sort the Data column that means simple join with its result can do the trick. You don't need ROW_NUMBER() or any sorting effort here. Have a temp table store Splits result and LEFT JOIN the two. This works for me.

CREATE TABLE #Input (PMID varchar(10))
INSERT INTO #Input SELECT Data FROM dbo.Split(@PMID, ',')

SELECT 
   jsn.*
FROM 
   #Input spl INNER JOIN ADMIN_Publication_JSON jsn on spl.PMID = jsn.PMID

Output: Returns the set in the order passed in @PMID

answered Nov 10 '18 at 21:34

Nikhil Vartak

5,002
3
26
32

What if we change it to `ADMIN_Publication_JSON jsn INNER JOIN #Input spl on spl.PMID = jsn.PMID`? Does it works? and why we need to use a TempTable (create it and insert the data) while we can just use a window function? – Ilyes Nov 10 '18 at 21:40
No it will not because in that case it will take up the records' order from `ADMIN_Publication_JSON` table. What's the issue with `LEFT JOIN` if that gives simpler query and desired output order. You can filter out `null` records from result if that worries you. What's say? – Nikhil Vartak Nov 10 '18 at 21:46
I did not say my answer is better. These are just different possible ways of doing what OP needs. – Nikhil Vartak Nov 10 '18 at 21:49

Zohar Peled · Answer 3 · 2018-12-06T07:49:29.893

I'm sorry to say but the currently accepted answer (by Sami) is wrong.

The problem with this answer is that it use ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) to get the order of the items in the comma delimited string, but since the order by is done on select null, what actually happens is that the row_number will assign the numbers in an arbitrary order - that may or may not match the order of the strings in the source string.
For more information, read Conor Cunningham's No Seatbelt – Expecting Order without ORDER BY.

If your split UDF returns a table with two columns, where one contains the substring and the other contains it's index, like Jeff Moden's DelimitedSplit8K, then simply use the ItemNumber (or equivalent) column for the order by. If it only returns a single column containing the substrings, you can use this a nice trick I've learned from Aaron Bertrand's Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions - it will guarantee to return the correct order of the substrings as long as they are unique.

A simple change on Sami's answer will give you correct results as long as the substrings are unique within the comma delimited string - Instead of ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), use CHARINDEX(',' + Value + ',', ',' + @Ids + ','), which will return the index of each substring inside the comma delimited string:

CREATE TABLE T(
  ID INT,
  SomeValue VARCHAR(45)
);

INSERT INTO T VALUES
(1, 'One'),
(2, 'Two'),
(3, 'Three'),
(4, 'Four'),
(5, 'Five');

DECLARE @IDs VARCHAR(200) = '3,5,2';

SELECT T.*
FROM T 
INNER JOIN 
       (SELECT Value,
               CHARINDEX(',' + Value + ',', ',' + @Ids + ',') AS Seq
       FROM dbo.Split(@Ids, ',')
       ) TT
       ON T.ID = TT.Value
ORDER BY TT.Seq;

Precise sort order of records that were found in a IN split function in SQL Server

3 Answers3