Solved

Custom SQL field with MIN() in SELECT

Posted on 2014-09-16
11
175 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
[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
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 40326419
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
ID: 40326435
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
ID: 40326490
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

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

Expert Comment

by:sdstuber
ID: 40326545
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
 

Author Comment

by:szadroga
ID: 40326556
I think i need to use the rank() function?

case
when rank(created_at) = 1 then role_name
end

would that work?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40326562
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 74

Expert Comment

by:sdstuber
ID: 40326565
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
ID: 40326577
can u elaborate on the subquery technique?  I am not too familiar with them.
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40327303
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 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 40327882
>>>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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

724 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