CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2005
Razort4x
Razort4x

Reputation: 3406

Why is IN operator showing wrong results?

I have this table BarCode, when I do a in query like this 

SELECT Barcode 
FROM BarcodeTable 
WHERE BarCode IN 
            (
                '53-3-1',
                '51-1-1',
                '51-2-1',
                '50-10-1',
                '50-8-1',
                '51-4-1',
                '50-1-1'
            )

why do I get 

53-3-1
50-1-1
50-8-1
50-10-1
51-1-1
51-2-1
51-4-1

Instead of ????

53-3-1
51-1-1
51-2-1
50-10-1
50-8-1
51-4-1
50-1-1

Why does SQL changing the order of barcodes? Because of the way SQL is ordering them (by itself) is causing an error in a dynamic query that is being executed by sp_executesql that relies on the order I send the input. Why is the SQL changing input order by itself?

51-4-1

Upvotes: 1

Views: 90

Answers (4)

Pratik Gohil
Pratik Gohil

Reputation: 21

Try Below Code

SELECT Barcode ,CASE Col_Order WHEN '53-3-1'  THEN 1
                      WHEN '51-1-1'  THEN 2
                      WHEN '51-2-1'  THEN 3
                      WHEN '50-10-1' THEN 4
                      WHEN '50-8-1'  THEN 5
                      WHEN '51-4-1'  THEN 6
                      WHEN '50-1-1'  THEN 7 
                      END     
FROM BarcodeTable 
WHERE BarCode IN 
            (
                '53-3-1',
                '51-1-1',
                '51-2-1',
                '50-10-1',
                '50-8-1',
                '51-4-1',
                '50-1-1'
            )    
ORDER BY Col_Order

Upvotes: 0

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391306

IN does not imply any ordering of the final result:

This SQL:

... WHERE x IN (1, 2, 3)

Should produce the same results as this:

... WHERE x IN (3, 2, 1)

The IN clause only filters the rows, basically, for each row you can think of it as just asking "should this row be part of the resultset?".

To get a specific ordering, you're going to have to add an ORDER BY clause to your statement.

To get a specific order, you can use an inline table:

SELECT Barcode
FROM BarcodeTable
    inner join (values
        ('*53-3-1*',  1),
        ('*51-1-1*',  2),
        ('*51-2-1*',  3),
        ('*50-10-1*', 4),
        ('*50-8-1*',  5),
        ('*51-4-1*',  6),
        ('*50-1-1*',  7)) as DummyTable (value, sortorder)
    on BarCode = value
order by
    sortorder

This will order by the sortorder field of the DummyTable.

You can not trick SQL Server into using the IN clause itself for ordering, you have to add an ORDER BY in some form or another.

You can also use a CASE WHEN ... expression to produce the sort order value depending on the barcode:

SELECT Barcode
FROM BarcodeTable
WHERE BarCode IN ('*53-3-1*', '*51-1-1*', '*51-2-1*', '*50-10-1*', '*50-8-1*', '*51-4-1*', '*50-1-1*')
ORDER BY
    CASE BarCode
        WHEN '*53-3-1*'  THEN 1
        WHEN '*51-1-1*'  THEN 2
        WHEN '*51-2-1*'  THEN 3
        WHEN '*50-10-1*' THEN 4
        WHEN '*50-8-1*'  THEN 5
        WHEN '*51-4-1*'  THEN 6
        WHEN '*50-1-1*'  THEN 7
    END

As the comment by Lieven suggests, there's an alternative on SQL Server 2005, using the WITH clause:

WITH DummyTable (value, sortorder) AS (
    SELECT '*53-3-1*' AS value,  1 AS sortorder
    UNION ALL
    SELECT '*51-1-1*',  2
    UNION ALL
    SELECT '*51-2-1*',  3
    UNION ALL
    SELECT '*50-10-1*', 4
    UNION ALL
    SELECT '*50-8-1*',  5
    UNION ALL
    SELECT '*51-4-1*',  6
    UNION ALL
    SELECT '*50-1-1*',  7
)
SELECT Barcode
FROM BarcodeTable
    inner join DummyTable
    on BarCode = value
order by
    sortorder

(note, I'm no expert in the usage of WITH, the above was just something I hacked together, but seems to work)

Upvotes: 3

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

To make your query order as you're expecting, you need to add an ORDER BY, or the ordering will be unreliable. Something like this should work but depending on your volume of barcodes it may in the end be easier to order it correctly in code once the result is returned;

SELECT Barcode 
FROM BarcodeTable 
WHERE BarCode IN ('*53-3-1*','*51-1-1*','*51-2-1*','*50-10-1*',
                  '*50-8-1*','*51-4-1*','*50-1-1*')
ORDER BY CASE BarCode WHEN '*53-3-1*'  THEN 1
                      WHEN '*51-1-1*'  THEN 2
                      WHEN '*51-2-1*'  THEN 3
                      WHEN '*50-10-1*' THEN 4
                      WHEN '*50-8-1*'  THEN 5
                      WHEN '*51-4-1*'  THEN 6
                      WHEN '*50-1-1*'  THEN 7
                      END

Upvotes: 1

Devolus
Devolus

Reputation: 22074

Unless you specify an order by statement, you should not rely on particular order in the output. You must provide an ORDER BY to get what you expect.

Upvotes: 3

Related Questions