CODEWITHSUNDEEP
sql
josh
josh

Reputation: 540

MySQL custom sorting

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

Upvotes: 2

Views: 72

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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.

Upvotes: 5

Praveen
Praveen

Reputation: 1449

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.

Upvotes: 0

Oleksi
Oleksi

Reputation: 13097

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.

Upvotes: 0

Related Questions