CODEWITHSUNDEEP
sql-server
DDan
DDan

Reputation: 8276

SQL Server query items with IN query but keep order

I have to query specific order of string IDs example data:

| ID   | RES  |
---------------
| A_12 | 1.89 |
| B_27 | 4.53 |
| B_28 | 1.02 |
| C_23 | 2.67 |

A tool generated a specific order which does not follow any standard ordering rule, and I cannot change that order. I am getting ~20000 of these rows and the RES is misaligned. I'd like to make a simple query which would collect all needed records by a list IDs and would give me a custom defined ordered list of results.

Something like:

SELECT RES FROM TABLE1 WHERE ID IN ('A_12', 'C_23', 'B_28', 'B_27')

and I'd lke it to return

1.89 
2.67
1.02
4.53

I understand IN query would not follow order as under the hood it most likely gets translated to (ID = A OR ID = B OR ID = C) query.

How do I enforce the result of the IN query to maintain my defined order? Do I need to create a temp table with one column for maintaining order? Any good solutions?

Upvotes: 2

Views: 1912

Answers (5)

SNR
SNR

Reputation: 772

Just a different approach:

SELECT RES FROM TABLE1 WHERE ID IN ('A_12')
UNION ALL
SELECT RES FROM TABLE1 WHERE ID IN ('C_23')
UNION ALL
SELECT RES FROM TABLE1 WHERE ID IN ('B_28')
UNION ALL
SELECT RES FROM TABLE1 WHERE ID IN ('B_27')

I supposed that the JOIN option is more efficent than this approach. If you want to automatize this option:

DROP TABLE #TABLE1

CREATE TABLE #TABLE1(ID NVARCHAR(4), RES FLOAT)

INSERT INTO #TABLE1 VALUES('A_12',1.89)
INSERT INTO #TABLE1 VALUES('B_27',4.53)
INSERT INTO #TABLE1 VALUES('B_28',1.02)
INSERT INTO #TABLE1 VALUES('C_23',2.67)

DECLARE @ID TABLE(ID NVARCHAR(4) not null);

--HERE HAVE TO INSERT IN ORDER YOU WANT TO RETURN THE RESULTS IN THE QUERY
insert into @ID VALUES('A_12')
insert into @ID VALUES('B_27')
insert into @ID VALUES('B_28')
insert into @ID VALUES('C_23')

DECLARE @UNIONALL  NVARCHAR(10)  = CHAR(13) + N'UNION ALL' 
DECLARE @QUERY     NVARCHAR(MAX) = NULL
DECLARE @ID_SEARCH NVARCHAR(4)   = NULL

DECLARE C CURSOR FAST_FORWARD FOR SELECT ID FROM @ID
OPEN C 

FETCH NEXT FROM C INTO @ID_SEARCH

SET @QUERY = N'SELECT RES FROM #TABLE1 WHERE ID = ''' + @ID_SEARCH + ''' '

FETCH NEXT FROM C INTO @ID_SEARCH

WHILE @@FETCH_STATUS = 0 BEGIN
    SET @QUERY = @QUERY + @UNIONALL
    SET @QUERY = @QUERY +  N' SELECT RES FROM #TABLE1 WHERE ID = ''' + @ID_SEARCH + ''' '

    FETCH NEXT FROM C INTO @ID_SEARCH 
END

EXECUTE master..sp_executesql @QUERY

Upvotes: 0

sepupic
sepupic

Reputation: 8687

Instead of temp table you can use values where you specify the desired order in the additional column, like this:

declare @table1 table(id varchar(10), res decimal(10,2));
insert into @table1 (id, res)
values 
('A_12', 1.89),
('B_27', 4.53),
('B_28', 3.54),
('C_23', 2.67);

select t.*
from @table1 t
     join (values(1, 'A_12'), (2, 'C_23'), (3, 'B_28'), (4, 'B_27')) v(id,val)
        on t.id = v.val
order by v.id;

@Table1 here is a substitute of your physical Table1.

Upvotes: 3

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Use JOIN instead of using IN and explicitly specify your order:

DECLARE @Test TABLE (
    ID  VARCHAR(32),
    RES DECIMAL(5,2)
)

INSERT @Test (ID, RES)
VALUES
('A_12', 1.89),
('B_27', 4.53),
('B_28', 3.54),
('C_23', 2.67)


SELECT t.ID, t.RES
FROM @Test t
    JOIN (
        VALUES
        ('A_12', 1),
        ('C_23', 2),
        ('B_28', 3),
        ('B_27', 4)
    ) o(ID, OrderId) ON t.ID = o.ID
ORDER BY o.OrderId

Upvotes: 3

DDan
DDan

Reputation: 8276

Do I need to create a temp table with one column for maintaining order

This seems to be working:

create table #tmp
(
  CustomOrder int,
  ID varchar(100)
)

insert into #tmp values (1, 'A_12')
insert into #tmp values (2, 'C_23')
insert into #tmp values (3, 'B_28')
insert into #tmp values (4, 'B_27')

query:

SELECT RES FROM TABLE1 INNER JOIN #tmp ON TABLE1.ID = #tmp.ID WHERE TABLE1.ID IN ('A_12', 'C_23', 'B_28', 'B_27')
ORDER BY #tmp.CustomOrder

output:

1.89 
2.67
1.02
4.53

Any better and easier solution?

Upvotes: 0

TomTom
TomTom

Reputation: 62157

There is no order to keep.

Returns of a select are NOT ORDERED by SQL basic definition, UNLESS YOU DEFINE AN ORDER.

So, there is no order to keep. Period.

If you want to keep one, use a temporary table / table variable for the valeus in IN (and obviously a join) and order by an order you also keep in a second field in said variable.

And no, this is not new - SQL is based on the SET theorem ever since Cobb published his famous paper back in the 1960s or so and never had order in returned results outside of side effects of implementation.

Upvotes: 1

Related Questions