CODEWITHSUNDEEP
sqlsql-order-by
404ram
404ram

Reputation: 33

Customised Ordering in SQL

I have a SQL query like 

SELECT column1,column2 
FROM table1 
WHERE column1 IN('q1','q2','q3')

The results are shown in the order:

q1
q2
q3

If I change the query to 

SELECT column1,column2 
FROM table1 
WHERE column1 IN('q3','q1','q2')

Still the order remains same.

How to achieve this ordering in the sql statement?

Help!!

Thanks

Upvotes: 3

Views: 802

Answers (6)

Mark Byers
Mark Byers

Reputation: 839214

One way to solve this is to add an extra column in your table called for example 'SortOrder' that contains integers determining in which order the rows should be returned. For example, you can set SortOrder to 1 for q3, 2 for q1 and 3 for q2. The you can use the following SELECT statement to return the rows in the order you need:

SELECT column1,column2 
FROM table1 
WHERE column1 IN('q3','q1','q2')
ORDER BY SortOrder

Upvotes: 2

Dostee
Dostee

Reputation: 744

An ORDER BY clause would help; however q1, q2, q3 or would have to be able to be sorted logically like alphabetically or if a date them by earliest to latest. That would look like this:

SELECT column1,column2 
FROM table1 
WHERE column1 IN('q1','q2','q3') 
ORDER BY column1 ASC

'or DESC if you would like the reverse 'also you can address the column in reference to its position instead of by its actual name like this:

SELECT column1,column2 
FROM table1 
WHERE column1 IN('q1','q2','q3') 
ORDER BY 1 ASC

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

Your RDBMS can't give you any guarantee that result set will be ordered by q1, q2, q3. Even if your in clause is in('q1', 'q2', 'q3'). If you need to enforce a particular order, use order by:

SELECT column1, column2 
  FROM table1 
 WHERE column1 IN('q1','q2','q3')
 ORDER BY column1

Also, please do include the RDBMS you are using. It might help answering the question in some cases.

Upvotes: 1

user151841
user151841

Reputation: 18066

You need an ORDER BY statement. That's the only way to control the order of a result set.

Upvotes: 2

Ben S
Ben S

Reputation: 69412

To reverse the order of the columns:

SELECT column2, column1 
FROM table1 WHERE column1 IN('q1','q2','q3')

To sort the rows, add an ORDER BY clause:

SELECT column1, column2 
FROM table1 WHERE column1 IN('q1','q2','q3') 
ORDER BY column1 DESC

You can order by any column, and you can set it to ascending (ASC) or descending (DESC).

You can even sort by more than one column:

SELECT column1, column2 
FROM table1 WHERE column1 IN('q1','q2','q3') 
ORDER BY column1 DESC, column2 ASC

This would sort in descending order based on column1 and would break ties using column2.

Upvotes: 0

Jonathan Feinberg
Jonathan Feinberg

Reputation: 45364

Yes. If only there were some way to ORDER a SQL result BY something.

Upvotes: 6

Related Questions