Oracle SQL Developer Query - Select most recent record and count by another column -


i novice sql , sure more simple can think of. looking recent record on table , count recent record status. ability return count status , month/year

would bonus.  table following: id     status     timestamp 1      active     04/19/2016 1      pending    04/18/2016 2      active     04/08/2016 2      pending    04/01/2016 3      pending    04/07/2016 4      pending    12/01/2015 5      cancelled  12/30/2015 

when run query wanting following:

active    04/2016 2 pending   04/2016 1  cancelled 12/2015 1  

i able recent record using following:

select id,     status,     date  ( select table_a,      status,      date,     row_number() on (partition id order date desc) col table_a) ps ps.col = 1; 

thank patience in advance.

you of way there. rest aggregation:

select status, to_char(date, 'yyyy-mm') yyyymm, count(*) (select table_a, status, date,              row_number() on (partition id order date desc) seqnum       table_a      ) ps seqnum = 1 group select status, to_char(date, 'yyyy-mm') ; 

Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -