Select records with order of IN clause

问题:

I have

SELECT * FROM Table1 WHERE Col1 IN(4,2,6)

I want to select and return the records with the specified order which i indicate in the IN clause (first display record with Col1=4, Col1=2, ...)

I can use

SELECT * FROM Table1 WHERE Col1 = 4
UNION ALL
SELECT * FROM Table1 WHERE Col1 = 6 , .....

but I don't want to use that, cause I want to use it as a stored procedure and not auto generated.

回答1:

You have a couple of options. Simplest may be to put the IN parameters (they are parameters, right) in a separate table in the order you receive them, and ORDER BY that table.

回答2:

I know it's a bit late but the best way would be

SELECT *
FROM   Table1
WHERE  Col1 IN( 4, 2, 6 )
ORDER  BY CHARINDEX(CAST(Col1 AS VARCHAR), '4,2,67')

Or

SELECT CHARINDEX(CAST(Col1 AS VARCHAR), '4,2,67')s_order,
       *
FROM   Table1
WHERE  Col1 IN( 4, 2, 6 )
ORDER  BY s_order

回答3:

The solution is along this line:

SELECT * FROM Table1 
WHERE Col1 IN(4,2,6)
ORDER BY 
    CASE Col1 
    WHEN 4 THEN 1
    WHEN 2 THEN 2
    WHEN 6 THEN 3
    END

回答4:

select top 0 0 'in', 0 'order' into #i
insert into #i values(4,1)
insert into #i values(2,2)
insert into #i values(6,3)

select t.* from Table1 t inner join #i i on t.[in]=t.[col1] order by i.[order]

回答5:

Replace the IN values with a table, including a column for sort order to used in the query (and be sure to expose the sort order to the calling application):

WITH OtherTable (Col1, sort_seq)
     AS 
     (
      SELECT Col1, sort_seq
        FROM (
              VALUES (4, 1), 
                     (2, 2), 
                     (6, 3)
             ) AS OtherTable (Col1, sort_seq)
     )
SELECT T1.Col1, O1.sort_seq
  FROM Table1 AS T1
       INNER JOIN OtherTable AS O1
          ON T1.Col1 = O1.Col1
 ORDER 
    BY sort_seq;

In your stored proc, rather than a CTE, split the values into table (a scratch base table, temp table, function that returns a table, etc) with the sort column populated as appropriate.

回答6:

I have found another solution. It's similar to the answer from onedaywhen, but it's a little shorter.

SELECT sort.n, Table1.Col1
FROM (VALUES (4), (2), (6)) AS sort(n)
JOIN Table1
ON Table1.Col1 = sort.n

回答7:

I am thinking about this problem two different ways because I can't decide if this is a programming problem or a data architecture problem. Check out the code below incorporating "famous" TV animals. Let's say that we are tracking dolphins, horses, bears, dogs and orangutans. We want to return only the horses, bears, and dogs in our query and we want bears to sort ahead of horses to sort ahead of dogs. I have a personal preference to look at this as an architecture problem, but can wrap my head around looking at it as a programming problem. Let me know if you have questions.

CREATE TABLE #AnimalType (
  AnimalTypeId      INT         NOT NULL PRIMARY KEY
  , AnimalType      VARCHAR(50) NOT NULL
  , SortOrder       INT         NOT NULL)

INSERT INTO #AnimalType VALUES (1,'Dolphin',5)
INSERT INTO #AnimalType VALUES (2,'Horse',2)
INSERT INTO #AnimalType VALUES (3,'Bear',1)
INSERT INTO #AnimalType VALUES (4,'Dog',4)
INSERT INTO #AnimalType VALUES (5,'Orangutan',3)

CREATE TABLE #Actor (
  ActorId           INT         NOT NULL PRIMARY KEY
  , ActorName       VARCHAR(50) NOT NULL
  , AnimalTypeId    INT         NOT NULL)

INSERT INTO #Actor VALUES (1,'Benji',4)
INSERT INTO #Actor VALUES (2,'Lassie',4)
INSERT INTO #Actor VALUES (3,'Rin Tin Tin',4)
INSERT INTO #Actor VALUES (4,'Gentle Ben',3)
INSERT INTO #Actor VALUES (5,'Trigger',2)
INSERT INTO #Actor VALUES (6,'Flipper',1)
INSERT INTO #Actor VALUES (7,'CJ',5)
INSERT INTO #Actor VALUES (8,'Mr. Ed',2)
INSERT INTO #Actor VALUES (9,'Tiger',4)



/* If you believe this is a programming problem then this code works */
SELECT  *
FROM    #Actor a
WHERE   a.AnimalTypeId IN (2,3,4)
ORDER   BY case when a.AnimalTypeId = 3 then 1
                when a.AnimalTypeId = 2 then 2
                when a.AnimalTypeId = 4 then 3 end

/* If you believe that this is a data architecture problem then this code works */              
SELECT  *
FROM    #Actor a
JOIN    #AnimalType at ON a.AnimalTypeId = at.AnimalTypeId
WHERE   a.AnimalTypeId IN (2,3,4)
ORDER   BY at.SortOrder


DROP TABLE #Actor
DROP TABLE #AnimalType