title: SQL Condition based dataset
body: <p>I have a SQL Server database that I did not design. 
  The employees have degrees, licensures and credentials stored in a few different tables. 
  I have written the query to join all of this information together so I can see an over all result of what the data looks like. I have been asked to create a view for this data that returns only the highest degree they have obtained and the two highest certifications.
  The problem is, as it is pre existing data, there is no hierarchy built into the data. All of the degrees and certifications are simply stored as a string associated with their employee number. 
  The first logical step was to create an adjacency list(I believe this is the correct term).
  For example 'MD' is the highest degree you can obtain in our list. So I have given that the "ranking" of 1. The next lower degree is "ranked" as 2. and so forth. 
  I can join on the text field that contains these and return their associated rank. 
  The problem I am having is returning only the two highest based on this ranking. 
  If the employee has multiple degrees or certifications they are listed on a second or third row. From a logical standpoint, I need to group the employee ID, First name and Last name. Then some how concatenate the degrees, certifications and licensures based on the "ranking" I created for them. It is not a true hierarchy in the way that I am thinking about it because I only need to know the highest two and not necessarily the relationship between the results.</p>
  
  <p>Another potential caveat is that the database must remain in SQL Server 2000 compatibility mode.</p>
  
  <p>Any help that can be given would be much appreciated. Thank you.</p>
  
  <pre><code>select a.EduRank as 'Licensure Rank',
     b.EduRank as 'Degree Rank',
     EmpComp.EecEmpNo, 
    EmpPers.EepNameFirst, 
     EmpPers.EepNameLast, 
     RTRIM(EmpEduc.EfeLevel),
     RTRIM(EmpLicns.ElcLicenseID),
     a.EduType,
     b.EduType
         from empcomp 
        join EmpPers on empcomp.eeceeid = EmpPers.eepEEID
        join EmpEduc on empcomp.Eeceeid = EmpEduc.EfeEEID
        join EmpLicns on empcomp.eeceeid = EmpLicns.ElcEEID
        join yvDegreeRanks a on a.EduCode = EmpLicns.ElcLicenseID
        join yvDegreeRanks b on b.EduCode = EmpEduc.EfeLevel
  </code></pre>