MySQL custom sorting

Question

While writing a C# program I am trying to do a batch retrieval from a SQL database instead of getting each record one at a time. This way I get all the records in one DataSet and can just hit the server once. I have found that this method can be significantly faster. However I need to maintain the order. Does anyone have any idea on how to accomplish this?

SELECT [UserID], [LastName], [FirstName]
FROM [users]
WHERE [UserID] = '2024443' OR [UserID] = '2205659' OR [UserID] = '2025493';

Edit:

I need to obtain the data ordered in this way:

2024443
2205659
2025493

Which order? The only order that you can specify is through an ORDER BY clause. — Steve
– Steve, Commented Oct 7, 2013 at 20:09
I am sorry I did not explain that clear enough. They have to be in the order I have them list in my select statement left to right. So 2024443 then 2205659 and then 2025493. — josh
– josh, Commented Oct 7, 2013 at 20:24
Out of curiosity: why that order? Is it really ordered by some other value in the records? Seems a little arbitrary — ChrisLively
– ChrisLively, Commented Oct 7, 2013 at 21:04

Mosty Mostacho · Accepted Answer · 2013-10-07 20:41:41Z

The appropriate query to run would be:

SELECT [UserID], [LastName], [FirstName]
FROM [users]
WHERE [UserID] IN ('2024443', '2205659', '2025493')
ORDER BY [UserID]

Provided that you want to keep the order in the results for the [UserID] column.

Edit after clarification on a very weird order the OP is looking for:

So the expected output will have the selected rows in this order:

'2024443'
'2205659'
'2025493'

So a simple ORDER BY that will order as a character field won't be enough. You should clarify what order it is because clearly you just want to sort based on those 3 rows only (eg: you haven't clarified where you would like to have number 2100000 and it is unpredictable).

For these kind of sorting you could go for an awful solution that will only work on those rows but as I said before, that's all you've provided. So you can do something like this:

SELECT [UserID], [LastName], [FirstName]
FROM [users]
WHERE [UserID] IN ('2024443', '2205659', '2025493')
ORDER BY
    CASE [UserID]
        WHEN '2024443' THEN 0
        WHEN '2205659' THEN 1
        ELSE 2
    END

You should be able to build the rest of the queries with that custom sorting. Just follow this as a template.

Oleksi · Accepted Answer · 2013-10-07 20:09:09Z

0

Does ORDER BY not work?

SELECT [UserID]
  ,[LastName]
  ,[FirstName]
FROM [users]
WHERE [UserID] = '2024443' OR [UserID] = '2205659' OR [UserID] = '2025493'
ORDER BY [UserID];

This will return the records you requested, sorted by UserID.

answered Oct 7, 2013 at 20:09

Oleksi

13.1k5 gold badges58 silver badges81 bronze badges

Comments

Praveen · Accepted Answer · 2013-10-07 20:12:47Z

0

If I undestand correctly, you are looking at retrieving the records in batches with specific batch size. In this case, you could retrieve the next set of records based on a primary key column index or a row number.

answered Oct 7, 2013 at 20:12

Praveen

1,44916 silver badges25 bronze badges

Collectives™ on Stack Overflow

MySQL custom sorting

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related