group by分组的一个问题

[0轰隆隆0]

id num flag

1 1 1
1 2 2
1 3 3
2 1 1
3 1 1
3 2 2
4 1 1
4 2 3
6 1 1

三个字段都是数字类型

我想得到
id num flag
1 3 3
2 1 1
3 2 2
4 2 3
6 1 1

根据ID分组，找num最大的那一行，我总拿不到flag的值啊

[CT_LXL]

引用 楼主 fulong258 的回复:

id num flag 1 1 1 1 2 2 1 3 3 2 1 1 3 1 1 3 2 2 4 1 1 4 2 3 6 1 1 三个字段都是数字类型 我想得到 id num flag 1 3 3 2 1 1 3 2 2 4 2 3 6 1 1 根据ID分组，找num最大的那一行，我总拿不到flag的值啊

with t as
 (select 1 id, 1 num, 1 flag
    from dual
  union all
  select 1 id, 2 num, 2 flag
    from dual
  union all
  select 1 id, 3 num, 3 flag
    from dual
  union all
  select 2 id, 1 num, 1 flag
    from dual
  union all
  select 3 id, 1 num, 1 flag
    from dual
  union all
  select 3 id, 2 num, 2 flag
    from dual
  union all
  select 4 id, 1 num, 1 flag
    from dual
  union all
  select 4 id, 2 num, 3 flag
    from dual
  union all
  select 6 id, 1 num, 1 flag from dual)
select t1.*, t2.flag
  from (select id, max(num) num from t group by id) t1, t t2
 where t1.id = t2.id
   and t1.num = t2.num
   order by t1.id;

[_拙计]

你分组的结果集 再和源表关联下 不就查出flag了

[snbxp]

WITH T AS
 (SELECT 1 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 1 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL
  SELECT 1 ID, 3 NUM, 3 FLAG FROM DUAL UNION ALL
  SELECT 2 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 3 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 3 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL
  SELECT 4 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 4 ID, 2 NUM, 3 FLAG FROM DUAL UNION ALL
  SELECT 6 ID, 1 NUM, 1 FLAG FROM DUAL)

SELECT ID, NUM, FLAG
  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM DESC) RN, T.*
          FROM T)
 WHERE RN = 1

[zhzhs007]

楼上正解，用分析函数就可以

[gangma2]

我认为先要从BB表中根据id分组和找出num最大的记录（这里顺便排了下序是结果和作者要求的一样），
然后连接BB表和上面查出来的结果做连接，取出别名为A的那个表的字段，具体如下：
select A.id,A.num,A.flag from BB A join
(select id,max(num) as num from BB group by id order by id) B
on B.id=A.id and B.num=A.num;
查询结果：

[浮云若水]

引用 3 楼 snbxp 的回复:

WITH T AS
 (SELECT 1 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 1 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL
  SELECT 1 ID, 3 NUM, 3 FLAG FROM DUAL UNION ALL
  SELECT 2 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 3 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 3 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL
  SELECT 4 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 4 ID, 2 NUM, 3 FLAG FROM DUAL UNION ALL
  SELECT 6 ID, 1 NUM, 1 FLAG FROM DUAL)

SELECT ID, NUM, FLAG
  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM DESC) RN, T.*
          FROM T)
 WHERE RN = 1

+1

[大脸猫o]

引用 3 楼 snbxp 的回复:

WITH T AS
 (SELECT 1 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 1 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL
  SELECT 1 ID, 3 NUM, 3 FLAG FROM DUAL UNION ALL
  SELECT 2 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 3 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 3 ID, 2 NUM, 2 FLAG FROM DUAL UNION ALL
  SELECT 4 ID, 1 NUM, 1 FLAG FROM DUAL UNION ALL
  SELECT 4 ID, 2 NUM, 3 FLAG FROM DUAL UNION ALL
  SELECT 6 ID, 1 NUM, 1 FLAG FROM DUAL)

SELECT ID, NUM, FLAG
  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM DESC) RN, T.*
          FROM T)
 WHERE RN = 1

+1

[齐岳]

学习了

[小灰狼W]

这个其实很简单 select id,max(num),max(flag)keep(dense_rank last order by num) from table1 group by id