表:
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 这条记录呢?
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 这条记录呢?
order by to_num( sunstr( id, 5, (length(id) - 4) ) )忘记函数了,查一下.
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
from a
where substr(a.id, 1, 4) = 'type' ;
select max(id) from a where substr(a.id,1,4) = 'type';我建表试了一下,没有问题。
from a
where id in (select top 1 id from a order by to_number(substr(id,5)) desc ) ;
from a
where substr(a.id, 1, 4) = 'type' ;