Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

get the second to last value using sql or postgresql

Posted on 2014-09-19
10
Medium Priority
?
249 Views
Last Modified: 2014-09-23
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

0
Comment
Question by:szadroga
  • 5
  • 3
10 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40333972
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
 

Author Comment

by:szadroga
ID: 40336718
The timestamp is the field called "timestamp" on the table event_log
0
 

Author Comment

by:szadroga
ID: 40337184
where do i place that piece of code in my current query?  Is this an additonal subquery i need to add?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:szadroga
ID: 40337196
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40337977
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
 

Author Comment

by:szadroga
ID: 40339362
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40339431
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
 

Author Comment

by:szadroga
ID: 40339814
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question