Go Premium for a chance to win a PS4. Enter to Win

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

Order by when have Union All

I want to order this union statement by sd.businessnameid (in alphbetic order). I tried
order by sd.businessname but it looks like it orders it by firstname + ' ' + lastname

What I have below doesnt work. I get this error:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.



  SELECT parentId = NULL, businessnameId = sd.id, NAME = sd.BusinessName, website = website FROM dbo.SignupDescription sd
   UNION ALL
  SELECT parentId = businessnameid, businessnameId = id, NAME = firstname + ' ' + LASTname, website ='' FROM dbo.SignupHCProvider
  --ORDER BY  SUBSTRING(sd.BusinessName,1,1) DESC

Open in new window

0
Camillia
Asked:
Camillia
  • 5
  • 5
1 Solution
 
sognoctCommented:
Select * from
  (SELECT parentId = NULL, businessnameId = sd.id, NAME = sd.BusinessName, website = website FROM dbo.SignupDescription sd
   UNION ALL
  SELECT parentId = businessnameid, businessnameId = id, NAME = firstname + ' ' + LASTname, website ='' FROM dbo.SignupHCProvider) t1
 ORDER BY  SUBSTRING(BusinessName,1,1) DESC


I'm writing with my cell phone .... There can be mistakes, just give a try
0
 
CamilliaAuthor Commented:
I keep getting businessname is invalid (the one substring). So, I changed it to "name" but still doesnt order it bu sd.businessname

Select * from
  (SELECT parentId = NULL, businessnameId = sd.id, NAME = sd.BusinessName, website = website FROM dbo.SignupDescription sd
   UNION ALL
  SELECT parentId = businessnameid, businessnameId = id, NAME = firstname + ' ' + LASTname, website ='' FROM dbo.SignupHCProvider) t1
 ORDER BY  SUBSTRING(t1.name,1,1) DESC
0
 
Anthony PerkinsCommented:
I want to order this union statement by sd.businessnameid (in alphbetic order).
If you want to order by  businessnameid then try it this way:
SELECT    parentId = NULL,
            businessnameId = sd.id,
            NAME = sd.BusinessName,
            website = website
  FROM      dbo.SignupDescription sd
  UNION ALL
  SELECT    parentId = businessnameid,
            businessnameId = id,
            NAME = firstname + ' ' + LASTname,
            website = ''
  FROM      dbo.SignupHCProvider
  ORDER BY  businessnameId

Open in new window


The trick when using a UNION statement is to use the alias and not the actual column as it will cause an error.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Anthony PerkinsCommented:
Do it this way:
ORDER BY  name DESC

But understand that it will be sorted by firstname + ' ' + LASTname
0
 
CamilliaAuthor Commented:
>> But understand that it will be sorted by firstname + ' ' + LASTname

No, I want to order it by sd.BusinessName

Now, for sd.BusinessName rows (first select)...the ParentId coulmn is null. Can this column be used to order by sd.BusinessName?
0
 
CamilliaAuthor Commented:
something like "order by Name where ParentId is null" to force it to order by sd.BusinessName
0
 
CamilliaAuthor Commented:
I want to do something like this link. This is where I got that SQL idea from

http://www.telerik.com/help/aspnet-ajax/treeview-data-binding-hierarchical.html

I want the result in a treeview, but ordered alphabetically
0
 
Anthony PerkinsCommented:
Your original question stated:
I want to order this union statement by sd.businessnameid (in alphbetic order)

Now you say:
No, I want to order it by sd.BusinessName

You really need to be clearer.

So if ORDER BY  name DESC does not do it for you, can you please explain how you want the rows in the second select sorted?
0
 
CamilliaAuthor Commented:
>> Now you say:
No, I want to order it by sd.BusinessName

I'm sorry, I made it a mistake in my orig post. I want it sorted by sd.BusinessName.

>>can you please explain how you want the rows in the second select sorted?

Can I sort by the first select? I dont think I can. I don't want ordered by firstname/lastname which is in the second select. I want it sorted by BusinessName which is in the first select.
0
 
Anthony PerkinsCommented:
But your not answering the question.  Where do you want to place the rows from the second select statement?  At the end, at the start, where?
0
 
Anthony PerkinsCommented:
If you want them at the end then simply do this:
ORDER BY parentId, NAME
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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