Custom SQL field with MIN() in SELECT

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
szadrogaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SheilsCommented:
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
szadrogaAuthor Commented:
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
SheilsCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

szadrogaAuthor Commented:
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
sdstuberCommented:
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
szadrogaAuthor Commented:
I think i need to use the rank() function?

case
when rank(created_at) = 1 then role_name
end

would that work?
0
sdstuberCommented:
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
sdstuberCommented:
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
szadrogaAuthor Commented:
can u elaborate on the subquery technique?  I am not too familiar with them.
0
PortletPaulfreelancerCommented:
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
sdstuberCommented:
>>>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.