orderby in sql query

Question

Hello,

I need to order sql query by a column (the three different values in this column are C,E,T).

I want the results in order of E,C,T. So, of course I can't use ascending or descending orderby on this column.

Any suggestions how can I do this? I don't know if that matters or not but I am using sybase data server on tomcat.

Answer 1

You could do it by putting a conditional in your select clause. i'm not Sybase guy but it might look something like this:

SELECT col, if col = 'E' then 1 else if col = 'C' then 2 else 3 end AS sort_col
FROM some_table
ORDER BY sort_col

If your AS alias doesn't work you could sort by column 1-based index like this:

ORDER BY 2

Answer 2

Idea is to add subquery with condition that will return your data row plus fictive value which will be 0 if there is E, 1 for E and 2 for T. Then simply order it by this column.

Hope it helps.

Answer 3

psasik's solution will work, as will this one (which to use and which is faster depends on what else is going on in the query):

select *
from some_table
where col = 'E'
UNION ALL
select *
from some_table
where col = 'C'
UNION ALL
select *
from some_table
where col = 'E'

that should work, but you can also do this which will be "safer" for large dataset which may be paged...

select *, 1 as o
from some_table
where col = 'E'
UNION ALL
select *, 2 as o
from some_table
where col = 'C'
UNION ALL
select *, 3 as o
from some_table
where col = 'E'
ORDER BY o

After I wrote the above I decided this is the best solution (note, I do not know if this will work on a sybase server as I don't have access to one right now but if it does not work on there just pull the creation of the keysort memory table out to a variable or temporary table -- which ever sybase supports)

;WITH keysort (k,o) AS
(
   SELECT 'E',0
   UNION ALL
   SELECT 'C',1
   UNION ALL
   SELECT 'E',2
)
SELECT * 
FROM some_table
LEFT JOIN keysort ON some_table.col = keysort.k
ORDER BY keysort.o

This should be the fastest of all choices -- uses in memory table to exploit sql's optimized joining.

Answer 4

You could use a per-row function to change the columns as other answers have stated but, if you expect this database to scale well, per-row functions are rarely a good idea.

Feel free to ignore this advice if your table is likely to remain small.

The advice here works because of a few general "facts" (I enclose that in quotation marks since it's not always the case but, in my experience, it mostly is):

1. The vast majority of databases are read far more often than they're written. That means it's usually a good idea to move the cost of calculation to the write phase rather than the read phase.
2. Most problems with database tend to be the "my query is slow" type rather than the "there's not enough disk space" type.
3. Tables always grow bigger than you thought they would :-)

If your situation is matched by those "facts", it makes sense to sacrifice a little disk space in order to speed up your queries. It's also better to incur the cost of calculation only when necessary (insert/update), not when the data hasn't actually changed (select).

To do that, you would create a new column (ect_col_sorted for example) in the table which would hold a numeric sort value (or more than one column if you want different soert orders).

The have an insert/update trigger so that, whenever a row is added to, or changed in, the table, you populate the sort field with the correct value (E = 1, C = 2, T = 3, anything else = 0). Then put an index on that column and your query becomes a much simpler (and faster):

select ect_col, other_col_1, other_col_2
from ect_table
order by ect_col_sorted;

Answer 5

The other methods work, but this is an often overlooked trick (in MSSQL, I'm not positive if it works in Sybase or not):

select
  foo,
  bar
from
  bortz
order by
  case foo 
    when 'E' then 1
    when 'C' then 2 
    when 'T' then 3 
    else 4 
  end