您正在查找每个ID的最大尝试次数，并显示按ID排序的结果？这是最基本的聚合：

select id, max(attempt)
from shrtckg
group by id
order by id;

字符串

这里是使用SQL Server的查询。这基本上是每个id的最大尝试值。

select 
       id , 
       max(attempt) as max_attempt from Student as a 
group by id;

字符串

如果你不想使用WHERE和GROUP BY（虽然不知道为什么），那么解决方案可以是MODEL子句（带Distinct）：

WITH  --  S a m p l e   D a t a : 
    tbl (ID, ATTEMPT) As
        (   Select 1,   1 From Dual Union All 
            Select 1,   2 From Dual Union All
            Select 1,   3 From Dual Union All
            Select 1,   4 From Dual Union All
            Select 2,   1 From Dual Union All
            Select 2,   2 From Dual 
        )

字符串
代码如下：

-- M a i n   S Q L :
SELECT  Distinct ID, COUNT_ATTEMPT "ATTEMPT"
FROM    (   Select  ID, ATTEMPT, 0 "COUNT_ATTEMPT" From tbl )
MODEL   Dimension By    ( ID, ATTEMPT   )
        Measures        ( COUNT_ATTEMPT )
RULES   (   COUNT_ATTEMPT[ANY, ANY] = Count(COUNT_ATTEMPT)[CV(ID), ATTEMPT > 0] )

型
... ...这是什么？

--    R e s u l t :
--          ID    ATTEMPT
--  ---------- ----------
--           1          4
--           2          2

型

你可以像这样使用标量子查询：

select distinct id, 
(select MAX(attempt) 
  from mytable t2
  where t2.id = t.id) as attempt
from mytable t

字符串

如果不能使用GROUP BY，我们可以使用窗口函数MAX()：

select distinct id, MAX(attempt) over (partition by id) as attempt
from mytable

字符串
Demo here