Solved

Custom SQL field with MIN() in SELECT

Posted on 2014-09-16
11
170 Views
Last Modified: 2014-09-22
I have the following SELECT statement and I am looking to create a field that will use the MIN() value on the created_at field.

I need to display what the role_name value is for the earliest value for the created_at field.

SELECT
    public.assets.last_name,
    public.assets.first_name,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'XXXXXXXXX'
        ELSE public.project_markets.market
    END,
    public.project_people_roles.name as role_name,
    public.project_instance_people.created_at
FROM
0
Comment
Question by:szadroga
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 16

Expert Comment

by:Sheils
Comment Utility
Try

Select TOP 1  public.assets.last_name,
    public.assets.first_name,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'XXXXXXXXX'
        ELSE public.project_markets.market
    END,
    public.project_people_roles.name as role_name,
    public.project_instance_people.created_at
FROM TableName Order By public.project_instance_people.created_at
0
 

Author Comment

by:szadroga
Comment Utility
That would work except I am going to have to return more than one record per user.  I will need to have duration in days from 1st login to last.  Is there way to just say take the value of role_name for the earliest created_at timestamp?
0
 
LVL 16

Expert Comment

by:Sheils
Comment Utility
I need a bit of clarification.

Are you saying that you want to pick out the first person to log in and then find the duration of each of his/her login?
0
 

Author Comment

by:szadroga
Comment Utility
I need to determine the earliest created_at date and what the value of the role_name is for that timestamp.  After this calculation, i will be bringing in additional fields to make other calculations.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
assuming your platform supports analytics, try something like this...


select * from
(SELECT
    public.assets.last_name,
    public.assets.first_name,
    CASE
        WHEN public.project_markets.market IS NULL
        THEN 'XXXXXXXXX'
        ELSE public.project_markets.market
    END,
    public.project_people_roles.name as role_name,
    public.project_instance_people.created_at,
row_number() over(partition by  public.assets.last_name,
    public.assets.first_name order by     public.project_instance_people.created_at) rn
FROM

)
where rn = 1
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:szadroga
Comment Utility
I think i need to use the rank() function?

case
when rank(created_at) = 1 then role_name
end

would that work?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
rank and row_number analytics are essentially the same use here.  

but no, the case usage isn't correct, you'll need to do the subquery to get the value scoped properly
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
rank and row_number are essentially the same use here.  

but no, the case usage isn't correct, you'll need to do the subquery to get the value scoped properly
0
 

Author Comment

by:szadroga
Comment Utility
can u elaborate on the subquery technique?  I am not too familiar with them.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
Comment Utility
I recommend row_number() instead of RANK() or DENSE_RANK() for this need. Although is is quite unlikely, RANK() or DENSE_RANK() are able to return more than one value of 1 for each "partition". ROW_NUMBER() cannot do this and a query based on ROW_NUMBER() will therefore always return a single record per partition when filtered for the value of 1.

Here you ORDER the row_number() by the field created_at ASC so that the earliest date is given the row_number value 1
I do not know what you need to partition by as you didn't provide the from clause details

As your given query is incomplete I can only guess at the existing FROM clause, but what you need to do is include the use of row_number() as a derived table (lines 8-10) and join that instead of the base table. Then, in addition, you add another join condition which you will see below on line 11 to only use the latest created_at records
SELECT      public.assets.last_name     , public.assets.first_name
          , COALESCE(public.project_markets.market,'XXXXXXXXX') AS market
          , ppr.name                                            AS role_name
          , ppr.created_at
FROM public.assets
      INNER JOIN public.project_markets ON public.assets.A = public.project_markets.A
      INNER JOIN (
                  SELECT X  , name , created_at
                      , ROW_NUMBER() OVER (PARTITION BY X ORDER BY created_at ASC) AS rn
                  FROM public.project_people_roles
            ) ppr ON public.assets.X = ppr.X AND ppr.rn = 1
;

Open in new window


Note: you cannot use row_number() and filter by row_number() in a single query, e.g. this would not work

select *, row_number() over(order by a_date) as rn from abc where rn = 1

It is necessary to do this:

select * from ( select *, row_number() over(order by a_date) as rn from abc ) x  where rn = 1
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
>>>can u elaborate on the subquery technique?

Sure, this particular type of subquery is also called an inline view.
Create a query that gives some result you want, wrap it in parentheses and then query that as if it were a table.

Simplified example:

select * from
     (select col1,col2 from some_table)     <----  This is the subquery/inline view
where col1 = 1234
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

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…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

12 Experts available now in Live!

Get 1:1 Help Now