Retrieve the second last record for each user from the database

I have a table that holds username, updated date, and status as below:

name | Updated_on | Status
akg     29-NOV-10       Active
akg     13-JAN-12       NonActive
akg     10-MAR-12       Active
ems     23-JUL-12       NonActive
ems     10-SEP-10       Active
tkp     10-SEP-10       NonActive
tkp     13-DEC-10       Active
tkp     02-JUL-12       NonActive
tkp     24-SEP-10       Active
aron    12-JAN-11       NonActive
aron    07-NOV-11       Active
aron    25-JUN-12       NonActive

In this table user status is updated every time we change the status (ie a username can have many entries as shown in the table.

I would like the second last updated record for each user. ie for the above table, the result should be:

name | Updated_on | Status
akg     13-JAN-12       NonActive
ems     10-SEP-10       Active
tkp     13-DEC-10       Active
aron    07-NOV-11       Active

I'm really confused as I want to get the record of each users in this.

Is there any query which can be used for this?

Thank you

Answers


You could try that, it's a bit verbose but it works:

SELECT
  name,
  max(Updated_on) as Updated_on,
  STATUS
FROM userstatus a
  WHERE (name, Updated_on) not in
  (select name, max(Updated_on) FROM userstatus group by name)
group by name, status
HAVING UPDATED_ON =
  (SELECT MAX(UPDATED_ON) FROM userstatus b where a.name = b.name
   and (b.name, b.Updated_on) not in
  (select name, max(Updated_on) FROM userstatus group by name)
  group by name);

Sqlfiddle


SELECT * FROM (
  SELECT
    rounda.*
  FROM
    userstatus AS rounda
    INNER JOIN userstatus AS roundb
      ON rounda.name=roundb.name
      AND rounda.Updated_on<roundb.Updated_On
  ORDER BY Updated_on DESC
) AS baseview
GROUP BY name

See sqlfiddle


As you tagged this (also) with Oracle:

select name, updated_on, status
from (
  select name, updated_on, status,
         row_number() over (partition by name order by updated_on desc) as rn,
         count(*) over (partition by name) as max_rn
  from userstatus
) 
where rn = max_rn - 1 
   or max_rn = 1;

This will actually work on a wide range of DBMS (including Oracle) - just not on MySQL.


Need Your Help

Positioning and zooming Google map inside RecyclerView

java android

Part of my application uses RecyclerView with CardView elements, and every CardView element contains Google Map which draws specific route. On every map I am zooming and positioning route inside of...

Why do I need to call a close() or shutdown() method?

java garbage-collection shutdown finalize

I'm new in Java with some background in C++ in my High School years. Now I'm trying to make something and I chose Java as the programming language.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.