Solved

Order by when have Union All

Posted on 2012-12-29
11
410 Views
Last Modified: 2012-12-29
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
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 9

Expert Comment

by:sognoct
ID: 38730127
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
 
LVL 7

Author Comment

by:Camillia
ID: 38730159
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38730162
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38730163
Do it this way:
ORDER BY  name DESC

But understand that it will be sorted by firstname + ' ' + LASTname
0
 
LVL 7

Author Comment

by:Camillia
ID: 38730170
>> 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
 
LVL 7

Author Comment

by:Camillia
ID: 38730181
something like "order by Name where ParentId is null" to force it to order by sd.BusinessName
0
 
LVL 7

Author Comment

by:Camillia
ID: 38730186
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38730198
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
 
LVL 7

Author Comment

by:Camillia
ID: 38730210
>> 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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38730228
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 38730230
If you want them at the end then simply do this:
ORDER BY parentId, NAME
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question