asked
select from one table where column maximum length in another table
Hello I have the following query resulting from a request SELECT SRN ,
SV.SN , Note FROM
dbo.SR JOIN dbo.SRN ON SR.SRID = SRN.SRID JOIN
dbo.SV ON SR.SID = SV.SID WHERE DT > '20110801 000000' AND DT < '20111101 000000' AND DATALENGTH(Note) < 200 The problem is for one SRN there can be multiple (notes). What I want to do is select a MAXIMUM of < 200 chars no matter how many notes have been entered against an SRN. How can this be done? Sorry hopefully if I show some data this will make it more clearer, the problem is we have lots of srns and lots of notes, some notes are few chars, some many. We're getting too many results and need to filter it down more, so maybe if there is a way of concatenating the note columns so that there's one row per SRN... SRN SN Note A/51/1 C bla A/51/1 A bla A/51/1 A bla bla bla bla bla A/51/1 A bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla B/25/1 P bla bla B/25/1 I bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla B/25/1 M bla bla bla bla bla bla bla bla bla bla B/25/1 M bla B/25/1 M bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla B/25/1 M bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla bla so ideally I would like one row per SRN, with the note columns all concatenated for a total maximum of 200 chars... so if one SRN has lots of little notes, just stick them all together is this possible? Finished Query Below! SELECT SRN , SN, SUBSTRING((SELECT ',' + CAST(Note AS VARCHAR(max)) FROM SRNote1 WHERE SRNote.srID = SRNote1.SrID FOR XML PATH('')),2,201) AS Notes FROM SRNote INNER JOIN SR ON SRNote.srID = SR.srID INNER JOIN S ON SR.sID = S.sID WHERE Dt > '20110801 000000' AND Dt < '20111101 000000' AND Sn = 'com' GROUP BY SRN, srnote.SrID, Sn ORDER BY SRN
It is not entirely clear what you want. If there are multiple notes, you want the longest note (but maximum 200 chars long)? What about the scenario where two notes are 150 chars long? Which "wins"? Maybe test tables and data with expected output would help me understand you.
Is this is what you want DECLARE @id TABLE (id int) DECLARE @name TABLE (id int , name TEXT); INSERT @id VALUES(1); INSERT @id VALUES(2); INSERT @name VALUES (1,'mmmmmmmmm'); INSERT @name VALUES(2,'ttttttttttttttttttttt'); INSERT @name VALUES (1,'hhhhhhhhhhhhh'); INSERT @name VALUES(1,'ggggggggggggggggggggggggggg'); INSERT @name VALUES(2,'mmmmmm'); INSERT @name VALUES(2,'tttttttt'); INSERT @name VALUES(2,'rrrrrrrrrrrrr'); SELECT
N.id AS SRN, SUBSTRING((SELECT ',' + CAST([NAME] AS VARCHAR(MAX)) FROM @name AS N1 WHERE [N].id = [N1].id FOR XML PATH('')),2,201) AS Notes FROM @name AS N INNER JOIN @id AS I ON [N].[id] = [I].[id] GROUP BY [N].[id] This is just to get your question right. There would be room for optimization.
This is good to hear. You should note that comma is used as the delimiter to concatenate, so if comma/s already existed into your notes then you should change the delimiter. Moreover, is it safe to assume this is the accepted answer? Thanks.
can you please send us the query you have made and the DDL of the specific tables?
one way around could be to CAST your text field e.g. CAST(NotesField as VARCHAR(MAX))
Please check if this fulfil your need. declare @id table (id int) declare @name table (id int , name varchar(max)); insert @id values(1); insert @id values(2); insert @name values (1,'mmmmmmmmm'); insert @name values(2,'ttttttttttttttttttttt'); insert @name values (1,'hhhhhhhhhhhhh'); insert @name values(1,'ggggggggggggggggggggggggggg'); insert @name values(2,'mmmmmm'); insert @name values(2,'tttttttt'); insert @name values(2,'rrrrrrrrrrrrr'); With Data as ( select
i.id , n.name , RANK () over(partition by
i.id order by datalength(n.name)) as RN from @id i inner join @name n on
i.id =
n.id where DATALENGTH(n.name)< 20 ) select id , name from data d where RN = (select MAX(rn) from Data where id =
d.id )
I am not sure if above-mentioned is the required solution, but would definitely like to omit the join from it by Sorting the keys in DESC order. Please also note that RANK/DENSE_RANK would not make sure that only one specific id would be returned in the output. If there is a matching rank, all the same ranked would be in the output e.g. DECLARE @id TABLE (id int) DECLARE @name TABLE (id int , name varchar(max)); INSERT @id VALUES(1); INSERT @id VALUES(2); INSERT @name VALUES (1,'mmmmmmmmm'); INSERT @name VALUES(2,'ttttttttttttttttttttt'); INSERT @name VALUES (1,'hhhhhhhhhhhhh'); INSERT @name VALUES (1,'hhhhhhhhhhhhh'); -- added intentionally to show matching ranked values INSERT @name VALUES(1,'ggggggggggggggggggggggggggg'); INSERT @name VALUES(2,'mmmmmm'); INSERT @name VALUES(2,'tttttttt'); INSERT @name VALUES(2,'rrrrrrrrrrrrr'); WITH Data AS ( SELECT
i.id , n.name , RANK() OVER ( PARTITION BY
i.id ORDER BY DATALENGTH(n.name) DESC /* SORT IN DESC ORDER */) AS RN FROM @id i INNER JOIN @name n ON
i.id =
n.id WHERE DATALENGTH(n.name) < 20 ) SELECT id, [name], rn FROM data d WHERE RN = 1
above example looks fine to me... u can use the distinct here than if max length is same and id are also same.. SELECT distinct id, [name], rn FROM data d WHERE RN = 1
