这样的SQL该怎么写？（100分）

[sjg008]

表：
ID

TYPE1
TYPE2
TYPE3
TYPE4
.....
TYPE187
ORTHER1
ORTHER2
ORTHER3
ORTHER4
.....

现在我要取得前缀为“TYPE”的最大记录 即“TYPE187” ，如何来取？
我做的有问题， 只能取到“TYPE99” ， 下面是我的SQL
SELECT a.id
FROM table a
WHERE substr(a.id, 1, 4) = 'type'
and ROWNUM = 1 order by a.id desc

有什么办法来取得ID = TYPE187 这条记录呢？

[lmby]

order by to_num( sunstr( id, 5, (length(id) - 4) ) )

忘记函数了,查一下.

[沝林]

SELECT a.id
FROM table a
WHERE substr(a.id, 1, 4) = 'type'
and ROWNUM = 1 order by a.id desc
--------------------------
有2个问题：
首先order by a.id desc是字符串比较，type99 就要比type111大，可以改成order by to_number(substr(id,5)) desc
其次rownum = 1是在排序之前的rownum，最好在子查询外取得
select id from (
SELECT a.id FROM table a WHERE substr(a.id, 1, 4) = 'type' order by to_number(substr(id,5)) desc)
where rownum =1

[hlp912]

楼上的可行

[hlp912]

select type||max(to_number(substr(id,5)))
from a
where substr(a.id, 1, 4) = 'type' ;

[yown]

这是字符与数字的比较,应该把字符转换成数字再比较

select *
from a
where id in (select top 1 id from a order by to_number(substr(id,5)) desc ) ;

[lee_billiy]

楼上的是sql server的写法吧，试试SELECT a.id FROM table a WHERE substr(a.id, 1, 4) = 'type' and ROWNUM = 1 order by to_number(substr(a.id, 5)) desc

[yearlist]

真奇怪，直接用max会有问题？
select max(id) from a where substr(a.id,1,4) = 'type';

我建表试了一下，没有问题。

[沝林]

楼上的，问你个问题，字符串比较'type9' 和 'type11'哪个大，不要告诉我是'type11'……

[yearlist]

duanzilin(寻) ,哦，知道了，谢谢啊。。。。

[littleboys]

select max(substr(a.id, 1, 4))||max(to_number(substr(id,5))) as ID from a WHERE substr(a.id, 1, 4)='TYPE';

[bixuehui]

select type||max(to_number(substr(id,5)))
from a
where substr(a.id, 1, 4) = 'type' ;