question

jhowe avatar image
jhowe 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
sql-server-2008t-sqlsql-server-2008-r2
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

help me pleaseee... :)
0 Likes 0 ·
with DATALENGTH(Note) < 200 you shouldn't get any note >= 200 characters no matter how many notes have been entered for a given SRN. Do you want to leave out the entire SRN if any of it's notes exceed 200 bytes?
0 Likes 0 ·
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.
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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.
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yes that's exactly what I want! I'll just try to reformat my query now!
0 Likes 0 ·
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.
0 Likes 0 ·
hi nearly there just getting an error data types varchar and text are incompatible in the add operator? think it's because notes is a text field and can't be compared etc...
0 Likes 0 ·
can you please send us the query you have made and the DDL of the specific tables?
0 Likes 0 ·
one way around could be to CAST your text field e.g. CAST(NotesField as VARCHAR(MAX))
0 Likes 0 ·
Show more comments
Neeraj Tripathi 1 avatar image
Neeraj Tripathi 1 answered
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 )
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
hi i've tried to clarify my question and provide a sample of data to make it more clear!
0 Likes 0 ·
Sri 1 avatar image
Sri 1 answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.