PGSQL 查询字段必须出现在GROUP BY中，但又不想作为分组依据，怎么更好的实现

Question

mysql中可以直接获取最大值所在列的任意字段
执行

select id,MAX(number) from test GROUP BY category

结果：
| id | MAX(number) |
| -- | -- |
| 1 | 15 |
| 2 | 23 |

但是PGSQL中查找的字段必须出现在GROUP BY中，

目前只能嵌套查询再查一次得到结果

总感觉有点怪怪的，是否有更好的实现方式呢？

mysql表

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `createdtime` date NULL DEFAULT NULL COMMENT '日期',
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '品类',
  `number` int(11) UNSIGNED NULL DEFAULT NULL COMMENT '数量',
  `finalamount` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `test` VALUES ('2021-06-02', 1, 'can', 10, 500);
INSERT INTO `test` VALUES ('2021-06-02', 2, 'pet', 23, 680);
INSERT INTO `test` VALUES ('2021-06-02', 3, 'can', 13, 570);
INSERT INTO `test` VALUES ('2021-05-13', 4, 'can', 15, 590);
INSERT INTO `test` VALUES ('2021-05-18', 5, 'pet', 17, 530);

pgsql表

DROP TABLE IF EXISTS "public"."testmember";
CREATE TABLE "public"."testmember" (
  "id" int4 NOT NULL DEFAULT nextval('testmember_id_seq'::regclass),
  "name" varchar COLLATE "pg_catalog"."default",
  "platstatus" int4,
  "mnumber" int4
);
ALTER TABLE "public"."testmember" ADD CONSTRAINT "testmember_pkey" PRIMARY KEY ("id");

INSERT INTO "public"."testmember" VALUES (1, 'aa', 1, 5);
INSERT INTO "public"."testmember" VALUES (2, 'bb', 2, 6);
INSERT INTO "public"."testmember" VALUES (3, 'cc', 1, 5);
INSERT INTO "public"."testmember" VALUES (4, 'aa', 1, 8);

Answer 1

select * from testmember a where (select count(1) from testmember b where b.plat status = a.platstat us and b.mnumber > a.mnumber ) < 1

Answer 2

你直接把ID字段放在group by 后面，其实并不会影响你的排序，你本身求的就是最大值。

Answer 3

你把想改的sql发一下文字方便复制粘贴