Topic: ask a question about database. @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / IT技术讨论 / ask a question about database.I have a table with a col is char, like 'A', 'S', 'E', I like to select the records order by 'S', 'A', 'E', how can I do it?

thanks -oasis(oasis); 2003-7-24 {147} (#1301976@0)

没明白你那是个什么Order // 进来好吗？最近去哪钓鱼了？那天去把头盔还给你，年初就说了的事，这大半年都过去了，还没办呢。 -bloor(不在Bloor的不老); 2003-7-24 (#1301986@0)

总去也，头盔先放你那里吧，肯定你现在还用得上，我们星期六有一个钓鱼活动，顺便作一下广告 -oasis(oasis); 2003-7-24 (#1302611@0)

create another table with two column, one is char, like 'A', 'S', 'E', the other set the order for char, then do a query again by link two table -cnyouyou(游啊游，游到外婆桥); 2003-7-24 (#1301998@0)
馊主意备份，update 掉，select出来，恢复原状 -oceandeep(北极熊·重新做人); 2003-7-24 {37} (#1301999@0)
thank you, guys, but I have 64000000 records in the table for one month, it's impossible to select twice from the table. -oasis(oasis); 2003-7-24 (#1302050@0)

我不太明白，你到底要干吗呢？不是查找吗？你有没有试过，还是就是凭感觉？不过是个简单的两个TABLE的inner jion，开销已经很小了。　 -guestagain(guest again); 2003-7-24 (#1302616@0)

use function order by:create or replace function test (col1 in char ) return number
is
return_value number;
BEGIN
if (col1='S') then return_value:=1 ;
else
if (col1='A') then return_value := 2;
else
if (col1='L') then return_value := 3; end if;
end if;
end if;
return return_value;
END test;
/
select * from yourtable order by test(col1); -tongcd(不明真相的总是群众); 2003-7-24 {348} (#1302095@0)
How about select ... where col1='A' union select ... where col1='S'... -smallguy(oops); 2003-7-24 (#1302151@0)
Select * from table order by iif(left([col],1)='s',1,col) -lilyba(sunshine困惑不懂装懂); 2003-7-24 (#1302275@0)
我用的不是oracle, sybase, 而是postgresql我觉着tongcd的方法最可行，可是postgresql的function好像不支持if/else, 可能只好用别的语言写一段了，如果再创建一个其他的table, 好想都不是好办法。 -oasis(oasis); 2003-7-24 {142} (#1302608@0)

那个table才3条数据也，同志，总比你每条数据都要做一堆判断快吧。 -guestagain(guest again); 2003-7-24 (#1302630@0)

Add one field ma; use trigger to fill this field. -ra_95(小人-龟心流水到天涯); 2003-7-25 (#1303384@0)

那你就query3遍再把结果放一起得了____ 谁这么缺德还这样order的 -zxcvb(朝天椒); 2003-7-24 (#1302620@0)
长期解决方案: 1. add new column "Order_Key", 2. update table set Order_key = decode(old_column, 'S', 1, 'A', 2, 'E', 3) 3. order by Order_key. Order_key can be used to for other situations and easyly changed -anno(烦人); 2003-7-25 (#1303265@0)

@Florida