Oracle数据库,分组排序后取第一条数据
数据格式
分组取第一条的效果
SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn, test1.* FROM test1) WHERE rn = 1 ;
Oracle数据库,分组排序后取第一条数据和最后一条,并且拼接名称
select id,wm_concat(name) as qsgt from ( select id,name from (-- 分组排序取出第一条和最后一条 select t1.id, t1.name, row_number() over(PARTITION BY t1.id order by t1.name) rowss1,--第一条 row_number() over(PARTITION BY t1.id order by t1.name desc) rowss2--倒数第一条 from t_test t1 where t1.pid= '0' ) where rowss1='1' or rowss2='1' ) GROUP BY id
不分组的话去掉
PARTITION BY t1.id
就行