-
Mar 3rd, 2021, 07:45 AM
#1
Thread Starter
Fanatic Member
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.
-
Mar 3rd, 2021, 08:58 AM
#2
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
-
Mar 3rd, 2021, 09:28 AM
#3
Thread Starter
Fanatic Member
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')
-
Mar 3rd, 2021, 10:03 AM
#4
Thread Starter
Fanatic Member
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.
-
Mar 3rd, 2021, 11:33 AM
#5
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|