get the second to last value using sql or postgresql

Can the MAX() be used to obtain the greatest and second greatest value from a field?  I am looking to get 2 timestamps, latest and second to latest.

SELECT
    MIN(events.event_log.timestamp)                          AS min_login_timestamp,
    MAX(events.event_log.timestamp)                          AS max_login_timestamp,
    DATE_PART ('day', MAX(events.event_log.timestamp)::timestamp - MIN(events.event_log.timestamp)::timestamp)
    || ' ' || 'd' AS duration_between_logins,
    DATE_PART ('day', now()::timestamp - MAX(events.event_log.timestamp)::timestamp) || ' ' || 'd' as days_since_last_login,
    public.assets.first_name || ' ' || public.assets.last_name AS team_member,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
    END,
    'n/a' as login_assigned_date,
    'n/a' as assigned_date_to_first_login
FROM
    events.event
INNER JOIN
    events.event_log
ON
    (
        events.event.id = events.event_log.event_id)
INNER JOIN
    public.assets_user_xref
ON
    (
        events.event_log.user_id = public.assets_user_xref.user_id)
INNER JOIN
    public.assets
ON
    (
        public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
    public.unitek_pcs
ON
    (
        public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
    public.project_pc_market_xref
ON
    (
        public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
    public.project_markets
ON
    (
        public.project_pc_market_xref.market_id = public.project_markets.id)
WHERE
    events.event_log.event_id IN (4,5)
   
GROUP BY assets.first_name, assets.last_name, project_markets.market;

Open in new window

szadrogaAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
Try this

Line 20 has changed
SELECT
    evlog.rn,
    MIN(evlog.timestamp)                          AS max_login_timestamp,
    DATE_PART ('day', MAX(evlog.timestamp)::timestamp - MIN(evlog.timestamp)::timestamp)
    || ' ' || 'd' AS duration_between_logins,
    DATE_PART ('day', now()::timestamp - MAX(evlog.timestamp)::timestamp) || ' ' || 'd' as days_since_last_login,
    public.assets.first_name || ' ' || public.assets.last_name AS team_member,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
    END,
    'n/a' as login_assigned_date,
    'n/a' as assigned_date_to_first_login
FROM
    events.event
INNER JOIN (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY event_id, user_id
                                     ORDER BY timestamp DESC) AS rn
            FROM events.event_log
      ) evlog  ON events.event.id = evlog.event_id
                        AND evlog.rn IN (1,2)          
INNER JOIN
    public.assets_user_xref
ON
    (
        evlog.user_id = public.assets_user_xref.user_id)
INNER JOIN
    public.assets
ON
    (
        public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
    public.unitek_pcs
ON
    (
        public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
    public.project_pc_market_xref
ON
    (
        public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
    public.project_markets
ON
    (
        public.project_pc_market_xref.market_id = public.project_markets.id)
        
WHERE
    evlog.event_id IN (4)
   
GROUP BY assets.first_name, assets.last_name, project_markets.market, evlog.event_id, evlog.rn;

Open in new window

0
 
PortletPaulfreelancerCommented:
I would suggest using row_number() for this http://www.postgresql.org/docs/9.1/static/functions-window.html

+ info on OVER() http://www.postgresql.org/docs/9.1/static/tutorial-window.html

You don't state which timestamp or table this should apply to, but here is an EXAMPLE of how it might be used in your query:

INNER JOIN (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY event_id
                                     ORDER BY timestamp DESC) AS rn
            FROM events.event_log
      ) evlog  ON events.event.id = evlog.event_id
                        AND evlog.rn IN (1,2)                --<< HERE is where you limit the result

Open in new window

0
 
szadrogaAuthor Commented:
The timestamp is the field called "timestamp" on the table event_log
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
szadrogaAuthor Commented:
where do i place that piece of code in my current query?  Is this an additonal subquery i need to add?
0
 
szadrogaAuthor Commented:
I added that piece of code to the end of  my query and it runs but I do not get a column for rn returned....

SELECT
    MIN(events.event_log.timestamp)                          AS min_login_timestamp,
    MAX(events.event_log.timestamp)                          AS max_login_timestamp,
    DATE_PART ('day', MAX(events.event_log.timestamp)::timestamp - MIN(events.event_log.timestamp)::timestamp)
    || ' ' || 'd' AS duration_between_logins,
    DATE_PART ('day', now()::timestamp - MAX(events.event_log.timestamp)::timestamp) || ' ' || 'd' as days_since_last_login,
    public.assets.first_name || ' ' || public.assets.last_name AS team_member,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
    END,
    'n/a' as login_assigned_date,
    'n/a' as assigned_date_to_first_login
FROM
    events.event
INNER JOIN
    events.event_log
ON
    (
        events.event.id = events.event_log.event_id)
INNER JOIN
    public.assets_user_xref
ON
    (
        events.event_log.user_id = public.assets_user_xref.user_id)
INNER JOIN
    public.assets
ON
    (
        public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
    public.unitek_pcs
ON
    (
        public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
    public.project_pc_market_xref
ON
    (
        public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
    public.project_markets
ON
    (
        public.project_pc_market_xref.market_id = public.project_markets.id)
INNER JOIN (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY event_id
                                     ORDER BY timestamp DESC) AS rn
            FROM events.event_log
      ) evlog  ON events.event.id = evlog.event_id
                        AND evlog.rn IN (1,2)          
        
WHERE
    events.event_log.event_id IN (4)
   
GROUP BY assets.first_name, assets.last_name, project_markets.market, event_log.event_id;

Open in new window

0
 
PortletPaulfreelancerCommented:
Oh dear, you don't just add this to your query. You REPLACE lines 17 to 21 with what I suggested (so you only need to access the event log once). This has the effect of putting an alias   evlog   on that table and all prior references to "events.event_log." become simply "evlog."

And you can then include evlog.rn in the select clause to output the 1 & 2 values
SELECT
    evlog.rn
    MIN(evlog.timestamp)                          AS max_login_timestamp,
    DATE_PART ('day', MAX(evlog.timestamp)::timestamp - MIN(evlog.timestamp)::timestamp)
    || ' ' || 'd' AS duration_between_logins,
    DATE_PART ('day', now()::timestamp - MAX(evlog.timestamp)::timestamp) || ' ' || 'd' as days_since_last_login,
    public.assets.first_name || ' ' || public.assets.last_name AS team_member,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
    END,
    'n/a' as login_assigned_date,
    'n/a' as assigned_date_to_first_login
FROM
    events.event
INNER JOIN (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY event_id
                                     ORDER BY timestamp DESC) AS rn
            FROM events.event_log
      ) evlog  ON events.event.id = evlog.event_id
                        AND evlog.rn IN (1,2)          
INNER JOIN
    public.assets_user_xref
ON
    (
        evlog.user_id = public.assets_user_xref.user_id)
INNER JOIN
    public.assets
ON
    (
        public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
    public.unitek_pcs
ON
    (
        public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
    public.project_pc_market_xref
ON
    (
        public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
    public.project_markets
ON
    (
        public.project_pc_market_xref.market_id = public.project_markets.id)
        
WHERE
    evlog.event_id IN (4)
   
GROUP BY assets.first_name, assets.last_name, project_markets.market, event_log.event_id;

Open in new window

0
 
szadrogaAuthor Commented:
I went ahead and used your code, but now I am only getting results for 1 user (2 rows of timestamps).  Prior to adding your code I was getting results for 6 users which is what I need.

SELECT
    evlog.rn,
    MIN(evlog.timestamp)                          AS max_login_timestamp,
    DATE_PART ('day', MAX(evlog.timestamp)::timestamp - MIN(evlog.timestamp)::timestamp)
    || ' ' || 'd' AS duration_between_logins,
    DATE_PART ('day', now()::timestamp - MAX(evlog.timestamp)::timestamp) || ' ' || 'd' as days_since_last_login,
    public.assets.first_name || ' ' || public.assets.last_name AS team_member,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'Corporate'
        ELSE public.project_markets.market
    END,
    'n/a' as login_assigned_date,
    'n/a' as assigned_date_to_first_login
FROM
    events.event
INNER JOIN (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY event_id
                                     ORDER BY timestamp DESC) AS rn
            FROM events.event_log
      ) evlog  ON events.event.id = evlog.event_id
                        AND evlog.rn IN (1,2)          
INNER JOIN
    public.assets_user_xref
ON
    (
        evlog.user_id = public.assets_user_xref.user_id)
INNER JOIN
    public.assets
ON
    (
        public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
    public.unitek_pcs
ON
    (
        public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
    public.project_pc_market_xref
ON
    (
        public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
    public.project_markets
ON
    (
        public.project_pc_market_xref.market_id = public.project_markets.id)
        
WHERE
    evlog.event_id IN (4)
   
GROUP BY assets.first_name, assets.last_name, project_markets.market, evlog.event_id, evlog.rn;
                                          

Open in new window

0
 
szadrogaAuthor Commented:
that appears to have done it.  thanks for all your help.  I am going to review in depth to get a strong understanding on how this is working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.