得到整行数据外面再套一下:select * from a where (product_name, close_date) in( select product_name, max(close_date) from a group by product_name )
SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,CLOSE_DATE FROM ( SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,ROW_NUMBER() PARTITION(ORDER BY COMPLETE_QTY) RN,CLOSE_DATE FROM TABLE_NAME ) WHERE RN =1;
上面那句有点问题,汗~~ SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,CLOSE_DATE FROM ( SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,ROW_NUMBER() OVER(PARTITION BY PRODUCT_NAME order by COMPLETE_QTY desc) rn,CLOSE_DATE FROM TABLE_NAME ) WHERE RN =1;
SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,CLOSE_DATE FROM ( SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,CLOSE_DATE ,ROW_NUMBER() OVER(PARTITION BY PRODUCT_NAME order by CLOSE_DATE desc) rn FROM TABLE_NAME ) WHERE RN =1;
这样行不? select * from (select *, rank()over(partition by name order by qty desc)ra from tabel) where ra=1;
select * from tab a where not exists(select 1 from tab b where a.product_name=b.product_name and a.close_date<b.close_date)
select * from ( select a.*,rank() over(partition by product_name order by close_date desc) as rank from a ) where rank=1
select /*+ use_nl(a b)*/a.* from a, (select product_name,max(close_date) close_date from a group by product_name) b where a.product_name=b.product_name and a.close_date=b.close_date
2。我想要得到的是整行数据而不仅仅是PRODUCT_NAME
select product_name, max(close_date) from a group by product_name )
FROM
(
SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,ROW_NUMBER() PARTITION(ORDER BY COMPLETE_QTY) RN,CLOSE_DATE
FROM TABLE_NAME
)
WHERE RN =1;
SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,CLOSE_DATE
FROM
(
SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,ROW_NUMBER() OVER(PARTITION BY PRODUCT_NAME order by COMPLETE_QTY desc) rn,CLOSE_DATE
FROM TABLE_NAME
)
WHERE RN =1;
FROM
(
SELECT WO_NUM,WO_LINE,PRODUCT_NAME,COMPLETE_QTY,CLOSE_DATE ,ROW_NUMBER() OVER(PARTITION BY PRODUCT_NAME order by CLOSE_DATE desc) rn
FROM TABLE_NAME
)
WHERE RN =1;
select * from (select *, rank()over(partition by name order by qty desc)ra from tabel) where ra=1;
用rank()不行,如下SQL> select * from b;
B1 B2
--------------------------------------- ----------
9999 001
9999 001
9999 002
8888 001
8888 002
7777 003
7777 004
6666 001
5555 001
9 rows selected
Executed in 0.093 seconds
SQL>
SQL>
SQL> select b1,b2,rank()over(partition by b1 order by b2) rk from b;
B1 B2 RK
--------------------------------------- ---------- ----------
5555 001 1
6666 001 1
7777 003 1
7777 004 2
8888 001 1
8888 002 2
9999 001 1
9999 001 1
9999 002 3
9 rows selected
Executed in 0.11 seconds
SQL> 某些情况下,rk要重复的。当然不是说本问题在逻辑上会出现这种情况。
几条记录共享一个最大值
那么一般情况下这几条都要查询出来才对
如果只查最大值而忽略其他字段又另当别论
那么取出所有COMPLETE_QTY最大的记录是理所当然,否则就没有意义了吧
select * from tab a
where not exists(select 1 from tab b where a.product_name=b.product_name and a.close_date<b.close_date)
(
select a.*,rank() over(partition by product_name order by close_date desc) as rank from a
) where rank=1
(select product_name,max(close_date) close_date from a group by product_name) b
where a.product_name=b.product_name
and a.close_date=b.close_date