Custom SQL field with MIN() in SELECT

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

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?

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?

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.

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

I think i need to use the rank() function?

case
when rank(created_at) = 1 then role_name
end

would that work?

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

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

can u elaborate on the subquery technique?  I am not too familiar with them.