SPROC to arrange results alphabetically, except top two results?

Question

I've got a UNIONed query which returns:

ReceiptFolderID FolderParentID FolderTypeID FolderType                                         FolderName                                         FolderDescription                                  ReceiptCount
--------------- -------------- ------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------
3               0              1            Pending Receipts                                   Pending Receipts                                   System folder; user cannot delete                  1
7               0              2            Uncategorized Receipts                             Uncategorized Receipts                             System folder; user cannot delete                  2
26              8              3            User-Defined Folder                                European Travel                                    Folders created by users                           0
25              0              3            User-Defined Folder                                Family Receipts                                    Folders created by users                           0



SELECT ReceiptFolderID, FolderParentID, tbl_ReceiptFolders.FolderTypeID, 
  FolderType,
   CASE tbl_ReceiptFolderTypes.FolderTypeID 
    WHEN 1 THEN tbl_ReceiptFolderTypes.FolderType 
    WHEN 2 THEN tbl_ReceiptFolderTypes.FolderType 
    ELSE tbl_ReceiptFolders.FolderName 
   END AS FolderName, 
  tbl_ReceiptFolderTypes.FolderDescription,
  dbo.GetFolderReceiptCount(ReceiptFolderID) AS ReceiptCount
INTO #tmp_UserFolders   
FROM tbl_ReceiptFolders LEFT JOIN tbl_ReceiptFolderTypes ON tbl_ReceiptFolders.FolderTypeID=tbl_ReceiptFolderTypes.FolderTypeID
WHERE UserID=@UserID 
AND (tbl_ReceiptFolderTypes.FolderTypeID = 1 
  OR tbl_ReceiptFolderTypes.FolderTypeID = 2)
--ORDER BY tbl_ReceiptFolderTypes.FolderTypeID ASC

UNION

SELECT ReceiptFolderID, FolderParentID, tbl_ReceiptFolders.FolderTypeID, 
  FolderType,
   CASE tbl_ReceiptFolderTypes.FolderTypeID 
    WHEN 1 THEN tbl_ReceiptFolderTypes.FolderType 
    WHEN 2 THEN tbl_ReceiptFolderTypes.FolderType 
    ELSE tbl_ReceiptFolders.FolderName 
   END AS FolderName, 
  tbl_ReceiptFolderTypes.FolderDescription,
  dbo.GetFolderReceiptCount(ReceiptFolderID) AS ReceiptCount
INTO #tmp_UserFolders
FROM tbl_ReceiptFolders LEFT JOIN tbl_ReceiptFolderTypes ON tbl_ReceiptFolders.FolderTypeID=tbl_ReceiptFolderTypes.FolderTypeID
WHERE UserID=@UserID 
AND (tbl_ReceiptFolderTypes.FolderTypeID <> 1 
  OR tbl_ReceiptFolderTypes.FolderTypeID <> 2)

As you can see, Pending and Uncategorized Receipts will always appear first, then the rest, alphabetically ordered.

Now, to throw this into an SPROC, which is what I've done with the #temp folders doesn't work.

What's the way around this to get the sproc to return these results?

Thanks!

Answer 1

In your second query, you have

(tbl_ReceiptFolderTypes.FolderTypeID <> 1          
 OR tbl_ReceiptFolderTypes.FolderTypeID <> 2)

Well, think about this... Every record will satisfy this predicate.. No matter what the value of FolderTypeID is, it is the case that it is either not 1, or it's not 2.

If I understand what you want correctly, try this:

Select ReceiptFolderID, FolderParentID,
    f.FolderTypeID, FolderType,
  Case t.FolderTypeID 
    When 1 Then t.FolderType
    When 2 Then t.FolderType                          
    Else f.FolderName End FolderName,   
  t.FolderDescription,
  dbo.GetFolderReceiptCount(ReceiptFolderID) ReceiptCount    
From tbl_ReceiptFolders f
   Left Join tbl_ReceiptFolderTypes t
       On f.FolderTypeID = t.FolderTypeID
Where UserID = @UserID 
Order By Case When t.FolderTypeID In (1,2) 
           Then 0 Else 1 End

Edit to add Count() try this:

Select ReceiptFolderID, FolderParentID,
    f.FolderTypeID, FolderType,
  Case t.FolderTypeID 
    When 1 Then t.FolderType
    When 2 Then t.FolderType                          
    Else f.FolderName End FolderName,   
  t.FolderDescription,
  Count(h.ReceiptFolderID) ReceiptCount    
From tbl_ReceiptFolders f
   Left Join tbl_ReceiptFolderTypes t
       On f.FolderTypeID = t.FolderTypeID
   Left Join tbl_ReceiptFolderLnk h 
       On h.ReceiptFolderID = f.ReceiptFolderID
Where UserID = @UserID
Group By ReceiptFolderID, FolderParentID,
    f.FolderTypeID, FolderType, t.FolderDescription,
  Case t.FolderTypeID 
    When 1 Then t.FolderType
    When 2 Then t.FolderType                          
    Else f.FolderName End,          
  Case When t.FolderTypeID In (1,2) 
    Then 0 Else 1 End
Order By Case When t.FolderTypeID In (1,2) 
           Then 0 Else 1 End