oracle子查询不能用ORDER BY 筛选第一条，并根据字符长度判断选择子查询

sql 示例如下：

根据字符串长度返回结果：
这里返回了len1=7 和len2=10

SELECT LENGTH( 'JPJZ147' ) AS len1, 10 AS len2 FROM dual )

子查询中排序筛选出第一条：

SELECT
				s.id
			FROM
				student s
			WHERE
				ROWID IN ( SELECT ROWID FROM ( SELECT * FROM student s WHERE st.t_no= 'JPJZ147' ORDER BY CREATE_DATE DESC ) WHERE ROWNUM <= 1 )

根据长度选择子查询

SELECT
	(
	CASE
			
			WHEN t.len1 > t.len2 THEN
			(
			SELECT
				s.id
			FROM
				student s 
			WHERE
				ROWID IN ( SELECT ROWID FROM ( SELECT * FROM student st WHERE st.no= 'JPJZ147' ORDER BY CREATE_DATE DESC ) WHERE ROWNUM <= 1 ) 
			) 
			WHEN t.len1 < t.len2 THEN
			(
			SELECT
				s.id
			FROM
				student s
			WHERE
				ROWID IN ( SELECT ROWID FROM ( SELECT * FROM student s WHERE st.t_no= 'JPJZ147' ORDER BY CREATE_DATE DESC ) WHERE ROWNUM <= 1 ) 
			) 
		END 
		) AS id
FROM
	( SELECT LENGTH( 'JPJZ147' ) AS len1, 10 AS len2 FROM dual ) t