asked on

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

PortletPaul

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

szadroga

ASKER

The timestamp is the field called "timestamp" on the table event_log

szadroga

ASKER

where do i place that piece of code in my current query? Is this an additonal subquery i need to add?

szadroga

ASKER

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

PortletPaul

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

szadroga

ASKER

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

szadroga

ASKER

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.