Order of records returned when doing an "IN"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Order of records returned when doing an "IN"

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-01 : 18:58:20

Is there anyway to force SQL to return the records in the order of the IDs contained in the "IN" clause...

select * from tblPleaseSayThereIs where UID in (2,3,6,1,8)

It returns them...

1
2
3
6
8

I understand why it does that but in this case I need them returned...

2
3
6
1
8

cvipin
Yak Posting Veteran

51 Posts

Posted - 2011-11-01 : 19:56:43

SELECT * FROM tblPleaseSayThereIs WHERE UID in (2,3,6,1,8)
ORDER BY
CASE
WHEN UID = 2 THEN 1
WHEN UID = 3 THEN 2
WHEN UID = 6 THEN 3
WHEN UID = 1 THEN 4
WHEN UID = 8 THEN 5
ELSE 6
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:35:38

you need a column on basis of which you specify order otherwise it returns according to random order it determine based on variety of factors like presence of index etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-02 : 10:27:33

quote:
Originally posted by cvipin

SELECT * FROM tblPleaseSayThereIs WHERE UID in (2,3,6,1,8)
ORDER BY
CASE
WHEN UID = 2 THEN 1
WHEN UID = 3 THEN 2
WHEN UID = 6 THEN 3
WHEN UID = 1 THEN 4
WHEN UID = 8 THEN 5
ELSE 6
END

There are 50,000 items in the "IN" :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 10:38:09

quote:
Originally posted by ferrethouse

quote:
Originally posted by cvipin

SELECT * FROM tblPleaseSayThereIs WHERE UID in (2,3,6,1,8)
ORDER BY
CASE
WHEN UID = 2 THEN 1
WHEN UID = 3 THEN 2
WHEN UID = 6 THEN 3
WHEN UID = 1 THEN 4
WHEN UID = 8 THEN 5
ELSE 6
END

There are 50,000 items in the "IN" :)

thats why i told there should be another column which will suggest on what basis you need ordering.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-11-02 : 11:00:36

quote:
Originally posted by ferrethouse

There are 50,000 items in the "IN" :)

I would expect the performance to be dire with 50,000 items in the IN, but maybe not?

In such circumstances we get the values into a table (#Temporary, or otherwise) and then JOIN it.

If you do that you can add an IDENTITY column to the "MyList" table, and ORDER BY that ID column

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-11-02 : 11:39:46

quote:
Originally posted by Kristen

quote:
Originally posted by ferrethouse

There are 50,000 items in the "IN" :)

I would expect the performance to be dire with 50,000 items in the IN, but maybe not?

In such circumstances we get the values into a table (#Temporary, or otherwise) and then JOIN it.

If you do that you can add an IDENTITY column to the "MyList" table, and ORDER BY that ID column

Yeah - it is just a one time thing. Client sent an excel document and needs a couple of columns filled in. I was hoping to just copy and paste the IDs into a select statement and then copy and paste the required fields back into the excel document in the same order but it doesn't seem possible.

You are correct. I will either have to get the guy to send the excel back ordered by the ID (as that is the field SQL seems to be ordering on) or import the excel into a table and do a join and then an export.

Thanks everyone for your insights.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-02 : 11:42:52

Without an Order By, the order of the results is not guaranteed. It won't necessarily be the ID or any other column, it could be entirely random.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-11-02 : 12:01:47

You could order it in Excel

Paste from Excel to make the IN list

Past results back into Excel (new sheet)

Insert a new column (on new sheet) with the RowNumber of the matching value from the original sheet (you'll probably have to insert a column into the original sheet and populate it with a RowNumber 1,2,3, ...

Use:
=VLOOKUP(NewSheetCell for the "IN" value, X:Y reference to the Original Sheet containing "IN" value, and RowNumber, FALSE)
but check that I have remembered the parameters correctly.

Then sort the New Sheet according to the RowNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources