Solved

get the second to last value using sql or postgresql

Posted on 2014-09-19
10
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 48

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
Independent Software Vendors: 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 48

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 48

Accepted Solution

by:
PortletPaul earned 500 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Current Month Filter in Visual Studio 10 48
Syntax issue with my Where Clause SQL 2012 20 42
T-SQL Query - Group By Year 3 55
MS Access Duplicate Data Assistance 9 23
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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