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
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
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
Anthony Perkins
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
I want the result in a treeview, but ordered alphabetically
Anthony Perkins
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?
Camillia
ASKER
>> 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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Anthony Perkins
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?
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
(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
I'm writing with my cell phone .... There can be mistakes, just give a try