Databases
StackOverflow2013
Postsdbo
POCross Apply using XML Path(comma delineated) - Remove Max Row if > 1
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCross Apply using XML Path(comma delineated) - Remove Max Row if > 1
    primarykey
    data
    text
    <p>What I want is if it returns more than 1 then remove the <strong>MAX([year]) Row</strong> but return the rest. Meaning if Cross Applyselect returns 4 records return only 3. If 3 then return only 2. If 2 it returns only 1. I believe the best way is to change the comma to another character (like #). Right search for # delete everything Right following. If I didn't use Cross Apply I would could have 3 rows Row(1)18 ----John Smith ---- BA ---- College1 --- 1990 Row(2) 18----John Smith ---MS---- Univ1------2003 Row(3)18----John Smith ----BA ----Univ2 -----2010.... I want to remove Row (3) because it is the students latest/current degree information. I only want the Prior degree(s), institution(s), year(s) not the current. Maybe a Right(rtrim(everthing after last comma) but is has to count Below is the SQL:</p> <pre><code>SELECT DISTINCT vw_name.personID AS personID, vw_name.FullName AS FullName, (CASE WHEN LEN(commaDegree) &lt;= 2 THEN commaDegree ELSE LEFT(commaDegree, LEN(commaDegree) - 1) END) AS commaDegree, (CASE WHEN LEN(commaInst) &lt;= 2 THEN commaInst ELSE LEFT(commaInst, LEN(commaInst) - 1) END) AS commaInst, (CASE WHEN LEN(commaYear) &lt;= 2 THEN commaYear ELSE LEFT(commaYear, LEN(commaYear) - 1) END) AS commaYear FROM vw_name LEFT JOIN vw_deg_all ON vw_deg_all.personID = vw_name.personID CROSS APPLY (SELECT isnull(degree.degree + N', ', N'') FROM vw_deg_all LEFT JOIN degree ON vw_deg_all.degreeID = degree.id WHERE vw_deg_all.personID = vw_name.personID ORDER BY vw_deg_all.[year] ASC FOR XML PATH('')) AS t (commaDegree) CROSS APPLY (SELECT isnull(institution.inst + N', ', N'') FROM vw_deg_all LEFT JOIN institution ON vw_deg_all.institutionID = institution.id WHERE vw_deg_all.personID = vw_name.personID ORDER BY vw_deg_all.[year] ASC FOR XML PATH('')) AS u(commaInst) CROSS APPLY (SELECT isnull(CONVERT(nvarchar, vw_deg_all.[year]) + N', ', N'') FROM vw_deg_all WHERE vw_deg_all.personID = vw_name.personID ORDER BY vw_deg_all.[year] ASC FOR XML PATH('')) AS v(commaYear) </code></pre> <p><strong>Resulting Query</strong></p> <p>PersonID -- FullName -- Comma Degree -- CommInst -- CommYear</p> <p>18 -- John Smith -- BA, MS, PHd -- College1, Univ1, Univ2 -- 1990, 2003,2010</p> <p>45 -- Paul Ryan -- BA, MS -- College15, Univ19 -- 1999, 2008</p> <p>134 -- Cindy Jones -- BA -- College56 -- 1978</p> <p>I only want to return the <strong>PRIOR</strong> degree(s), Institution(s), Year(s). <strong>Not the latest</strong> . Meaning PersonID 18 remove PHd, Univ2, 2010. PersonID 45 remove MS, Univ19, 2008. PersonID 134 leave as is since only 1 record.</p> <p>Hope this makes sense. Thanks in advance.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USsjackson5280
    1. USsjackson5280
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCross Apply using XML Path(comma delineated) - Remove Max Row if > 1
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload