Solved

Order by when have Union All

Posted on 2012-12-29
11
401 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
  • 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trigger for audit 26 71
SQL 2000:  Shrink LDF file 11 39
Need some help wiht :CAST AS Double 11 44
SQL Encryption question 2 50
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…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now