[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

How to list a specific record first in a list of records

ColdFusion 9
MS SQL Server 2005

This is another Careplanners question. =)

My query, getCareplanners, selects a list of careplanners from table CareplannersMembers, ordered by lastname.

However, Susan (CareplannersID = 7) would like her name to be first. So, I need to order the output of query getCareplanners by CareplannersID = 7, then lastname.

How can I do that? My Forta SQL book is not telling me how. Thanks for any advice.

Eric

<!--- query getCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="getCareplanners">
SELECT
<!--- select from parent table, CareplannersMembers cp --->
cp.CareplannersUUID
,cp.CareplannersID
,firstname
,lastname
,practice
,city1
,state1
,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 )
,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
                           FROM careplanners_has_certifications hc
                            inner join careplanners_certifications c on hc.certificationID = c.certificationID
                           WHERE hc.CareplannersID = cp.CareplannersID
                           ORDER BY c.certificationTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 )

FROM CareplannersMembers cp
ORDER BY lastname
  </cfquery>
0
Eric Bourland
Asked:
Eric Bourland
  • 4
  • 2
1 Solution
 
AmickCommented:
Try this
SELECT cp.CareplannersUUID
,cp.CareplannersID
,firstname
,lastname
,practice
,city1
,state1
,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 
,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
                           FROM careplanners_has_certifications hc
                            inner join careplanners_certifications c on hc.certificationID = c.certificationID
                           WHERE hc.CareplannersID = cp.CareplannersID
                           ORDER BY c.certificationTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 

FROM CareplannersMembers cp
WHERE CareplannersID = 7
UNION (
SELECT
<!--- select from parent table, CareplannersMembers cp --->
cp.CareplannersUUID
,cp.CareplannersID
,firstname
,lastname
,practice
,city1
,state1
,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 
,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
                           FROM careplanners_has_certifications hc
                            inner join careplanners_certifications c on hc.certificationID = c.certificationID
                           WHERE hc.CareplannersID = cp.CareplannersID
                           ORDER BY c.certificationTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 ) 

FROM CareplannersMembers cp
ORDER BY lastname
)

Open in new window

0
 
gdemariaCommented:
adding a UNION won't guarantee the order.. you need to modify the order by clause


Use a case statement to force ID 7 to the top and all others to sort 2nd..

order by  case when CareplannersID = 7 then 1 else 2, lastname
0
 
gdemariaCommented:
A more flexible approach would be to add a field in the carePlannerMembers table that indicates this type of person.   Who is Susan that she wants to be first, is she a manager or lead of some type?   Whatever that is, how about creating a flag on the record to indicate that..

carePlannerMembers table..

 add field isManager  bit default 0 not null

Then on Susan's record, you can change it to 1  

Then on the order by clause you have...

order by case when isManager = 1 then 1 else 2 end, lastName


This makes it easy for you to change it from Susan to someone else (which code changes), you can even have more than one person sorted to the top if desired, just by checking of the box for multiple people

0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
Eric BourlandAuthor Commented:
>>>approach would be to add a field in the carePlannerMembers table that indicates this type of person.   Who is Susan that she wants to be first, is she a manager or lead of some type?   Whatever that is, how about creating a flag on the record to indicate that..

Susan is the founder and director of the Life Care Planners network.

It makes a lot of sense to create as flag on the record. I will try that. More in a little while.

Thanks, gdemaria.

E
0
 
Eric BourlandAuthor Commented:
I set up column isManager. All records are set to value 0 or False, except for Susan, who is set to value 1.

I don't think I am doing teh Case correctly in the order by:


<!--- query getCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="getCareplanners">
SELECT
<!--- select from parent table, CareplannersMembers cp --->
cp.CareplannersUUID
,cp.CareplannersID
,firstname
,lastname
,practice
,city1
,state1
,isManager
,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
                           FROM careplanners_has_degrees hd
                            inner join careplanners_degrees d on hd.degreeID = d.degreeID
                           WHERE hd.CareplannersID = cp.CareplannersID
                           ORDER BY d.DegreeTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 )
,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
                           FROM careplanners_has_certifications hc
                            inner join careplanners_certifications c on hc.certificationID = c.certificationID
                           WHERE hc.CareplannersID = cp.CareplannersID
                           ORDER BY c.certificationTitle
                           FOR XML PATH( '' )
                          ), 3, 1000 )

FROM CareplannersMembers cp
ORDER BY
       <cfswitch expression="isManager">  
              <cfdefaultcase>isManager</cfdefaultcase>
              <cfcase value="LastName">LastName</cfcase>  
           </cfswitch>  

  </cfquery>
0
 
Eric BourlandAuthor Commented:
Wait, you meant actually:

ORDER BY case when isManager = 1 then 1 else 2 end, lastName

This works. =)
0
 
Eric BourlandAuthor Commented:
Thank you as always, gdemaria. Thank you Amick for your input; I do see where you were going with the UNION idea.

Eric
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now