CODEWITHSUNDEEP
hiveimpala
user2747058
user2747058

Reputation: 119

Selecting first row from a group after order

Here is what my table looks like:

appl_db_srvr_xref_id  secrty_ctl_id assmt_dt  cmdb_id  secrty_ctl_elemnt_impmtn_cd
       180                 43       1/1/2018    227         N 
       180                 43       9/31/2018   227         Y
       179                 28       1/1/2018    710         N
       179                 29       2/2/2018    710         N
       179                 43       3/3/2018    710         N

What I need is to group by (appl_db_srvr_xref_id, secrty_ctl_id, and cmdb_id) sort by date, and return the most current row. So in this instance I would like to return:

180   43   227   Y
179   28   710   N
179   29   710   N
179   43   710   N

what I am trying is: 

SELECT secrty_ctl_id, 
       appl_db_srvr_xref_id, 
       cmdb_id, 
       assmt_dt 
 FROM tablename 
 GROUP BY 
       secrty_ctl_id, 
       appl_db_srvr_xref_id, 
       cmdb_id 
 ORDER BY assmt_dt desc

Upvotes: 2

Views: 2187

Answers (1)

leftjoin
leftjoin

Reputation: 38325

Use row_number() analytic function:

select
       secrty_ctl_id, 
       appl_db_srvr_xref_id, 
       cmdb_id, 
       assmt_dt
from
(
SELECT secrty_ctl_id, 
       appl_db_srvr_xref_id, 
       cmdb_id, 
       assmt_dt,
       row_number() over (partition by appl_db_srvr_xref_id, secrty_ctl_id, cmdb_id order by assmt_dt desc) rn
 FROM tablename 
 )s
where rn=1;

Upvotes: 2

Related Questions