Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

get the second to last value using sql or postgresql

Posted on 2014-09-19
10
Medium Priority
?
236 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 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

670 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