Solved

Order by when have Union All

Posted on 2012-12-29
11
398 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

19 Experts available now in Live!

Get 1:1 Help Now