Customised Ordering in SQL

Question

Hi, 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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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