New simple question, from the group By-CodePudding

Table A
Id memberId lid createTime
1 111 22 20170810
2 222 33 20170809
3 111 22 20170811

Expect to find out results:
MemberId grouping and lid after the latest of the article, namely:

Id memberId lid createTime
2 222 33 20170809
3 111 22 20170811

CodePudding user response:

 with t as (
Select '1' id, '111' memberId, lid '22', '20170810' createTime from dual 
Union all 
Select '2', '222', '33', '20170809' the from dual 
Union all 
Select '3', '111', '22', '20170811' the from dual 
) 
Select * from t 
The where (memberId, lid, createTime) in 
(select memberId, lid, Max (createTime) from t group by memberId, lid) 
The order by id asc

CodePudding user response:

The select memberId, lid, createTime from
(select memberId, lid, createTime, row_number () over (partition by memberId, lid order by createTime desc) rw from A)
Where rw=1;

Max (createTime) may appear to repeat

CodePudding user response:

Select * from a a1 where not the exists (select 1 from a where clause memberId=a1. MemberId and lid=a1. The lid and id & gt; A1. Id);

CodePudding user response: