Solved

Custom SQL field with MIN() in SELECT

Posted on 2014-09-16
11
173 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 48

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 26 68
Query Syntax 17 43
T-SQL: New to using transactions 9 46
SQL Query - Database name 'TempDB' ignored, referencing object in tempdb - Error 2 34
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

860 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