让我们首先指出，没有列的排序是没有好主意。
通常，id是唯一的，并且会递增，因此按id排序就足够了。
如果不是这种情况，则至少应该有另一个具有有意义值的列（例如，也是递增数字或日期时间），可用于对结果进行排序。
因此，如果可能的话，您应该修复您的表设计，并添加这样的列或使您已经存在的id列唯一。
如果这是不可能的，并且您确实必须仅按行号排序，则可以执行以下操作：

SELECT id,
CASE WHEN rn = 1 THEN 'YES' ELSE 'NO' END AS first,
CASE WHEN rn = COUNT(*) OVER (PARTITION BY id) 
THEN 'YES' ELSE 'NO' END AS last 
FROM 
(
  SELECT
    id,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) rn
  FROM yourtable
);

如果你有一个列要排序（让我们把它命名为“rank”），这样会安全得多：

SELECT id,
CASE WHEN rn1 = 1 THEN 'YES' ELSE 'NO' END AS first,
CASE WHEN rn2 = 1 THEN 'YES' ELSE 'NO' END AS last
FROM 
(
  SELECT
   id,
   ROW_NUMBER() OVER (PARTITION BY id ORDER BY rank) rn1,
   ROW_NUMBER() OVER (PARTITION BY id ORDER BY rank DESC) rn2
  FROM yourtable
);

这里有一个选项：
示例数据：

SQL> with
  2  test (id, rank) as
  3    (select 111, 1 from dual union all
  4     select 111, 2 from dual union all
  5     select 111, 3 from dual union all
  6     select 222, 1 from dual union all
  7     select 222, 2 from dual
  8    ),

查询从此处开始：

9  temp as
 10    (select id,
 11       rank,
 12       first_value(rank) over (partition by id) rnk_min,
 13       last_value(rank)  over (partition by id ) rnk_max
 14     from test
 15    )
 16  select id,
 17    case when rank = rnk_min then 'Yes' else 'No' end first,
 18    case when rank = rnk_max then 'Yes' else 'No' end last
 19  from temp
 20  order by id, rank;

        ID FIRST   LAST
---------- ------- -------
       111 Yes     No
       111 No      No
       111 No      Yes
       222 Yes     No
       222 No      Yes

SQL>

If you don't have rows with the same rank per id, you may use lag/lead functions to mark first and last rows with a flag using default argument of these functions, which is used when the function leaves a window boundary.

with sample_tab (id, rank) as (
  select 111, 1 from dual union all
  select 111, 2 from dual union all
  select 111, 3 from dual union all
  select 222, 1 from dual union all
  select 222, 2 from dual
)
select
  id
  , lag('No', 1, 'Yes') over(partition by id order by rank asc) as last
  , lead('No', 1, 'Yes') over(partition by id order by rank asc) as last
from sample_tab

If the data may have the same rank for multiple rows per id, you may use the same technique (a case when function goes beyound window boundary) with coalesce .

with sample_tab (id, rank) as (
  select 111, 1 from dual union all
  select 111, 2 from dual union all
  select 111, 2 from dual union all
  select 222, 1 from dual union all
  select 222, 2 from dual
)
select
  id
  , coalesce(max('No') over(
      partition by id order by rank asc
      /*RANGE for logical offset,
      setting the same flag for a group of first/last rows*/
      range between 1 preceding and 1 preceding
  ), 'Yes') as first
  , coalesce(max('No') over(
      partition by id order by rank asc
      range between 1 following and 1 following
  ), 'Yes') as last
from sample_tab

fiddle