?
Solved

Custom SQL field with MIN() in SELECT

Posted on 2014-09-16
11
Medium Priority
?
181 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
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 1000 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 1000 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

Technology Partners: 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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to increase the row limit in Jasper Server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 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