Question

oracle中选出某个字段里面最大值的记录的sql语句怎么写

Answer 1

1、创建测试表，

create table test_max(id number, value number);

2、插入测试数据

insert into test_max values(1,12);

insert into test_max values(2,100);

insert into test_max values(3,55);

insert into test_max values(4,100);

insert into test_max values(5,50);

commit;

3、查询表中全量数据，select t.*, rowid from test_max t;

4、编写sql，使用rank分析函数，取value值为最大的记录; select t.* from (select t.*, rank() over(order by value desc) rk from test_max t) t where rk = 1;

Answer 2

实现例句如下：
SELECT a.*
FROM table1 a WHERE NOT
EXISTS (SELECT 1 FROM table1 b WHERE b.id>a.id)

或者
SELECT *
FROM table1 WHERE id IN
(SELECT Max(id) FROM table1)

又或者
SELECT *
FROM table1 WHERE id=(SELECT Max(id) FROM table1)