Recordset Order

[ebound]

I'm working on shuffling results in a recordset. I have written code that shuffles that id's in my 'In' Clause:

Select * from User where UserID in (1060, 1708, 3640, 496, 2255, 1059, 906, 1032, 1588, 899)

Each time the 'In' clause changes.

Select * from User where UserID in (1588, 1634, 1059, 1032, 496, 906, 2255, 1708, 899, 1060)

The problem is sql server returns the data in the order it wants to and not the order of my 'In' clause. The data is returned each time in the same order no matter what the order of the 'In' clause.

Does anyone know how to return the data in the order of the 'In' clause? Is this possible?

Thx.

[coopster]

Sorry ebound, but that's not how it works. The IN predicate merely tells the engine which rows to look at and return. For example, an IN clause equivalent could also be written as follows:

SELECT * FROM User WHERE 
 UserID = 1588 OR 
 UserID = 1634 OR  
...

So you see, there will be no sorting of the data. You'll want to look into an ORDER BY clause.

[CaseyRyan]

Instead of using an order by, you could use a UNION ALL statement .

Since you're already pieceing together the SQL statement it wouldn't be a big jump and it would solve your problem.

You would loop through the list of IDs and end up with a statement like this:

SELECT * FROM User WHERE UserID = 1060
UNION ALL
SELECT * FROM User WHERE UserID = 1708
UNION ALL
SELECT * FROM User WHERE UserID = 3640
UNION ALL
...

You want to make sure that you're using the UNION ALL statement for performance reasons. The UNION statement will go back and try to remove duplicates.

Because I imagine the UserID field in your User table is a clustered index, this should be VERY fast.

The only other optimizations I would suggest are to put the owner in front of the table and to specifically list out of columns that you are retrieving instead of using the star(*). Both of these will shorten the time to query.

It would end up like this.

SELECT UserID, UserEmail, UserName FROM dbo.User WHERE UserID = 1060
UNION ALL
...

-=casey=-

[coopster]

Welcome to WebmasterWorld, CaseyRyan, and good point.

[ebound]

Thanks CaseyRyan!

That's exactly what I was looking for. Performance is really much of an issue because the max number of records I'm returning is 10 with very little data in each so it works perfect.

THX

[txbakers]

the max number of records I'm returning is 10

That's not the performance issue - it's the number of records the DB has to search through to get your 10.

The more records you accumulate, the more it will slow down looking for specific ones.