Sorting SELECTed rows in SQL Server

Question

I was wondering if there is a way to get rows ordered NOT by a column in the table(so no simple option to use ORDER BY or something like that).
I tried both

SELECT *
FROM table
WHERE column IN ('A', 'B', 'C')

and

SELECT *
FROM table
WHERE column = 'A' OR column = 'B' OR column = 'C'

Then, the SqlDataReader in my C# application shows the rows in order like 'C', 'A', 'B' which is (probably) in sequence of the date written in the database.
What I meant is not an alphabetical order but the order the each variable itself shows in the query.

It isn't easy for me to search for the syntax in detail as a beginner of SQL Server. Also, I'm quite new here stack overflow, so let me know if I'm making any mistake. I would appreciate it if you do.
Thank you in advance.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

My bad. The given example was kind of ambiguous. Let me provide a clearer one.

SELECT *
FROM table
WHERE column IN ('r2', 'r4', 'r3', 'r1')

And I want my SqlDataReader object to read 'r2' first, 'r4' next, 'r3' and then 'r1'.
The application used to do it by querying for each single row using a loop and I was afraid that would harm the performance.

you have to add a sorting column , such as creation date , id , etc , and sort by that column — Raptor
– Raptor, Commented Jun 14, 2013 at 3:18
Where are you getting this new order from and what are you doing that you require arbitrary order? — Scott Chamberlain
– Scott Chamberlain, Commented Jun 14, 2013 at 4:57
@ScottChamberlain I'm writing a 'Sentence analyzer' for natural language. the table is a kind of a "dictionary" and 'r1' to 'r4' represent different words. That's why I had to keep the sequence. — Hannibal
– Hannibal, Commented Jun 14, 2013 at 5:23

Chris Allen · Accepted Answer · 2013-06-14 05:09:14Z

3

SELECT *
FROM table
WHERE (...expression...)
order by
case
when column='r2' then 1
when column='r4' then 2
when column='r3' then 3
when column='r1' then 4
else 9 end,column

answered Jun 14, 2013 at 5:09

Chris Allen

6053 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hannibal Over a year ago

Thank you so much. This is the answer I've been looking forward to. I'm sorry that I can't upvote this due to the reputation limit.

Vasanth · Accepted Answer · 2013-06-14 03:28:00Z

2

In most cases SQL Server will return results based on clustered index if no "order by" clause is specified. So you may want to check the primary key or clustered index on your table.

Another option is you can add a running sequence number column & make it primary key.

answered Jun 14, 2013 at 3:28

Vasanth

1,7101 gold badge13 silver badges17 bronze badges

Comments

Haney · Accepted Answer · 2013-06-14 03:19:04Z

0

Try:

SELECT *
FROM table
WHERE column IN ('A', 'B', 'C')
ORDER BY column DESC

To get it in C, B, A order.

answered Jun 14, 2013 at 3:19

Haney

35.2k9 gold badges61 silver badges71 bronze badges

1 Comment

Hannibal Over a year ago

O, I didn't meat that. I edited my question more clearly, so check it out again please.

Collectives™ on Stack Overflow

Sorting SELECTed rows in SQL Server

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related