Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

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

Avatar of sognoct
sognoct
Flag of Italy image

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
Avatar of Camillia

ASKER

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
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.
Do it this way:
ORDER BY  name DESC

But understand that it will be sorted by firstname + ' ' + LASTname
>> 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?
something like "order by Name where ParentId is null" to force it to order by sd.BusinessName
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
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?
>> 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.
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?
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial