Results 1 to 5 of 5

Thread: SQL - Selecting non-null value of a column when sequence number is max

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    SQL - Selecting non-null value of a column when sequence number is max

    Hi
    I have a challenge where I need to know the value of a column but only when there is a non-null value in it and also the nun-null record is the latest sequentially for that specific column.

    So, I have a table which I use to preserve traceability. Anything changes, a new row is inserted containing the changes (from and to) and the sequence of change for each case number is incremented.
    Each relevant field has two columns "From_XX" and To_XX". If a case is changed, those columns will hold the value what is changed form and to. If the change does only concerns specific field then the rest of the fields will contain nulls except for those field where a change of value is applied.
    All good with capturing the changes however, what I need now is to see the latest value for each column.

    For example I need to know what is the value for the field called "To_MyName" for 30 cases.

    If that can be also done for multiple columns (instead of only "To_MyName" then e.g. "To_MyName1" and "To_MyName2" ), it would be perfect.

    her eis my thoughts but I get nothing:
    Code:
    SELECT TopOrder.To_AlignmentQA As LastQA  
    FROM DV_Arena_Treacablity_Cases A
    INNER JOIN
    (SELECT To_AlignmentQA, MAX(ModificationOrder) AS MAXStuff
    FROM DV_Arena_Treacablity_Cases 
    GROUP BY To_AlignmentQA
    ) As TopOrder 
    
    ON A.To_AlignmnetQA = TopOrder.To_AlignmentQA 
    AND A.ModificationOrder = TopOrder.MAXStuff
    Where A.CaseID in ('1','2','3')
    Thanks for any help.
    Last edited by Grand; Mar 3rd, 2021 at 07:49 AM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: SQL - Selecting non-null value of a column when sequence number is max

    Some sample data would go a long way to help.
    But on that note, as far as I know, aggregate funcitons will ignore null vlaues in the dataset. so if you have 3, 5,8, null, 2,8, 1, null, 0 ... and you select MAX() on that... you should get 8... you'll also get a message about nulls being ignored too.

    If you're getting nothing with your query, then something is off with it.
    Break it down... start with the inner query, does it return the results you expect... once it does, then use a left join to join it to the other tble... that may give some insight as to where you're suddenly dropping records. If it isn't the inner join, then look at your where clause... are your caseIds really strings?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL - Selecting non-null value of a column when sequence number is max

    Thanks you for reaching out.
    Meanwhile, I found another approach that actually solved my issue here in a way but it requires a lots of text as I have some 30 columns:
    Code:
    SELECT DISTINCT 
            CaseID, 
            FIRST_VALUE(To_AlignmnetQA) OVER(PARTITION BY CaseID ORDER BY CASE WHEN To_AlignmnetQA is NULL then 0 else 1 END DESC, To_AlignmnetQA desc) AS To_AlignmnetQA,
            FIRST_VALUE(To_DVowner) OVER(PARTITION BY CaseID ORDER BY CASE WHEN To_DVowner is NULL then 0 else 1 END DESC, To_DVowner desc) AS To_DVowner,
    		FIRST_VALUE(To_Verified) OVER(PARTITION BY CaseID ORDER BY CASE WHEN To_Verified is NULL then 0 else 1 END DESC, To_Verified desc) AS To_Verified,
    		FIRST_VALUE(To_ErrorType1) OVER(PARTITION BY CaseID ORDER BY CASE WHEN To_ErrorType1 is NULL then 0 else 1 END DESC, To_ErrorType1 desc) AS To_ErrorType1
    FROM    DV_Arena_Treaciblity_Cases
    Where CaseID in ('1','2','3','4')

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2018
    Posts
    514

    Re: SQL - Selecting non-null value of a column when sequence number is max

    Not good i am getting these error now:

    Code:
    ORDER BY list of RANGE window frame has total size of 904 bytes. Largest size supported is 900 bytes
    
    ORDER BY list of RANGE window frame cannot contain expressions of LOB type -- This error is from a field with nvarchar(max)
    I need to have those fields. What shall I do?
    Last edited by Grand; Mar 3rd, 2021 at 10:17 AM.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: SQL - Selecting non-null value of a column when sequence number is max

    Again... some sample data will go a long way in getting help.
    we have no idea what you're dealing with. I'm not even sure what the data looks like, what the tables look like, or what the output should look like.
    So start with that. Can you give us a SQL script that will 1) create a sample table, 2) populate it with some sample data, then provide what some expected results should be?
    I have some ideas, but I don't know it's the right thing to do based on the descriptions. You have a comment about a line in regards to a VARCHAR(MAX) field being used in an order by .... for gods sake WHY?
    So, yeah, some details on the table sructure and the data in it would be of some use.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width