Solved

Latest columnn value in SQL or PostgreSQL

Posted on 2014-09-19
2
181 Views
Last Modified: 2014-09-26
I am trying to get the latest ...roles.name as role_name value.  My current query below returns one or many values of the roles.name for a team_member because they can have more than one role.  I am trying to just return the latest one.  I tried wrapping it in a MAX() function within the SELECT statement but no luck.

SELECT
    MIN(events.event_log.timestamp)                            AS min_login_timestamp,
    MAX(events.event_log.timestamp)                            AS max_login_timestamp,
    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,
    public.project_people_roles.name AS role_name
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
    public.project_instance_people
ON
    (
        public.assets.id = public.project_instance_people.asset_id)
INNER JOIN
    public.project_people_roles
ON
    (
        public.project_instance_people.project_people_role_id = public.project_people_roles.id)
WHERE
    events.event_log.event_id IN (4) OR project_people_roles.role_id IN (44, 45, 46)

GROUP BY assets.first_name, assets.last_name, project_markets.market, project_people_roles.name;

Open in new window

0
Comment
Question by:szadroga
2 Comments
 
LVL 22

Expert Comment

by:earth man2
Comment Utility
Window functions are your friend ....
http://www.postgresql.org/docs/9.2/static/tutorial-window.html

rank = 1 gives you the topmost value(s).
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
rank() can return more than a single 1 per partition (when there are ties in the data).

use row_number() for this need (it will only ever give a single 1 per partition)

see: 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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now