Solved

How do I order a union-part with letters before a union-part with numbers in MS SQL Server?

Posted on 2010-08-27
35
536 Views
Last Modified: 2012-05-10
How do I order a union-part with letters before a union-part with numbers in MS SQL Server?

I have a Union between two concatenations (columns concatenated into one column in each union-part). The first union-part contains only letters. The second union-part contains only digits/numbers.

When executing the query, I get the results ordered by the second union-part with only digits/numbers before the first union-part with only letters.

How do I change this so that the first union-part with letters is ordered first, before the second union-part with digits/numbers?
0
Comment
Question by:hermesalpha
  • 16
  • 12
  • 4
  • +1
35 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33547898
Any sample rows from each to work with?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33547905
For example, this puts all numbers (2nd part) before 1st part

select * from
(select Name='Jim Bem' union all select 'Tony Star' union all select 'Bobby Joe') x
union all
select convert(varchar(20),number) from
(select Number=123232 union all select 9991 union all select 5512) y
order by Name
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33547911
Add an additional column to each part of the union

with names as (
select Name='Jim Bem' union all select 'Tony Star' union all select 'Bobby Joe'),
numbers as (
select Number=123232 union all select 9991 union all select 5512)

select sorter=1, name from names
union all
select 2, convert(varchar(20),number) from numbers
order by sorter, name
0
 

Author Comment

by:hermesalpha
ID: 33547923
I forgot to mention that the digits/numbers are already formated as varchar. So everything on both sides of the Union are in format varchar.

I get the date 2010-xx-xx ordered before anything that begins with a letter. I want the reversed, anything beginning with a letter should be ordered before any digit (that is already in format varchar)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33547974
Add a static value to your union statements to get the first union statement to always return first.

Sample:

select columna, columnb
(
select 1 as firstsort, ColumnA, ColumnB from TableA
union all
select 2, ColumnC, ColumnD from tableB
) a
order by 1,2
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33547981
You can still use the code as shown - it just adds a sorter column to each part of the union.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33547993
Yup.  I missed that you had done that in your last post.  My bad CW!
0
 

Author Comment

by:hermesalpha
ID: 33548302
BrandonGalderisi,

I'm almost following your explanation, but not fully. First, let me add that I also have these statements for the first part: Inner join on, Where condition, Group by (then comes Union, not Union all). I don't want any extra column, so I think I could use your suggestion. Cyberkiwi, do you mean I get an extra column if I use your suggestion?

"Select 1": should I use this in the statement? And then the same columns as outside the parenthesis (columna=ColumnA, columnb=ColumnB)?

Also for the second part, I have Inner join, Where condition, Group by and finally Order by.

Do you mean I should write "Order by 1, 2" on the last line, exactly like that? (1=alias for columns A and B?)

Is "a" on the last but one line a typo?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33548385
Hi Author,

BrandonGalderisi means that

select 1 as DisplayOrder, ColumnA, ColumnB from TableA
union all
select 2, ColumnC, ColumnD from tableB
order by DisplayOrder,ColumnA

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33548389
Here

Just to show i use ColumnA in order condition. :-)
0
 

Author Comment

by:hermesalpha
ID: 33548547
What I have now is:

Select [concatenation1]
From...Inner join
On...
Where...
Group by...
Union
Select [concatenation2]
From...Inner join
On...
Where...
Group by...
Order by...

So BrandonGalderesi means?:

Select [concatenation 1]
From...Inner join
On...
Where...
Group by...
(
Select [concatenation 1] From Table A
Union
Select [concatenation 2] From Table B
)
Select [concatenation2]
From...Inner join
On...
Where...
Group by...
Order by [concatenation1], [concatenation2];



0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33548582

Best way is

Select  1 as DisplayOrder, columnList
From...Inner join
On...
Where...
Group by...
Union
Select 2,columnList
From...Inner join
On...
Where...
Group by...
Order by DisplayOrder,...

0
 

Author Comment

by:hermesalpha
ID: 33548825
Ok, I think I got it now. But this is the way I did from the beginning! Order by Displayorder I have always put last with the first select list (which is a concatenation into just one column). Therefore, I though it also would display the first part first, but it doesn't.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33548877
Author,

It should work.
can u please post your full code?

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33548965
This is what I meant. There aren't any new columns.
But put the entire select into a sub-select.  The additions are in bold.  Your original query is intact in normal font.

What I have now is:

Select <List all fields> --- no, do not include Sorter
From
(
Select Sorter=1, [concatenation1]
From...Inner join
On...
Where...
Group by...
Union
Select Sorter=2, [concatenation2]
From...Inner join
On...
Where...
Group by...
) SQ
Order by Sorter, ...

That is the general form to keep parts of a UNION query together. The following is specific to putting letters before numbers.

Select * From (Select [concatenation1]
From...Inner join
On...
Where...
Group by...
Union
Select [concatenation2]
From...Inner join
On...
Where...
Group by...
 ) SQ
Order by isnumeric(left,1), ...
 
0
 

Author Comment

by:hermesalpha
ID: 33548993
SELECT first concatenation
FROM Table A INNER JOIN Table B
ON column Aa = column Ba
WHERE column Aa=(fixed value)
GROUP BY columns from Table A
UNION
SELECT second concatenation
FROM Table A INNER JOIN Table B
      ON column Aa = column Ba
      INNER JOIN Table C
      ON column Bb = column Ca
      INNER JOIN Table D
      ON column Ca = column Da
WHERE column Aa=(fixed value)
GROUP BY columns from Table B
ORDER BY first concatenation
0
 

Author Comment

by:hermesalpha
ID: 33549003
The first concatenation begins with a letter, the second concatenation begins with a number. Even though I end the whole query with order by first concatenation the resulting display order is by first number. I tried adding a desc on the last line and only then it orders with the first concatenation first, but then all the numbers are also in descending order.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33549019
Hi hermesalpha

Have you looked at and tried either of the suggestions in http:#a33548965 above?
Make the changes EXACTLY AS SHOWN in bold.

Regards
0
 

Author Comment

by:hermesalpha
ID: 33549075
<List of fields>, is that my first concatenation? Because I can't just list the fields here, I need to write the concatenation I have. The fields without the concatenation doesn't make sense, they have to be concatenated. Will <List of fields> be visible when executing the query? If so, I need to write the concatenation here.

Is "Sorter=1" a statement, like Select? And I write a comma after it and then the concatenation?

"Order by Sorter,..." what comes after this? The first concatenation?

What is SQ?
0
 

Author Comment

by:hermesalpha
ID: 33549169
So this is what you are trying to get around with the parenthesis as I understand it?:

In a UNION query, you can only have one ORDER BY clause, at the end, and
it will apply to the whole result, not to one of the UNION'ed SELECT
statements.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33549177
Whatever your query is, just splice the text in bold AS-IS, letter for letter, word for word, from the 2nd option around it.
You should see that all your text including Order by is still present.
Just add where shown.
0
 

Author Comment

by:hermesalpha
ID: 33549203
I did as I understood it, but got this error message:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
0
 

Author Comment

by:hermesalpha
ID: 33549206
Line 19: it doesn't accept SQ after the parenthesis
Line 4: it doesn't accept (Sorter=1... either
0
 

Author Comment

by:hermesalpha
ID: 33549214
1st row+2nd line: Select concatenation 1
3rd row: (
4th row: Sorter=1, concatenation 1

19th row: Group by.....)SQ  
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33549222
Just to confirm, this is MS SQL Server right? 2000/5/8/R2?
We can keep guessing with your pseudo-SQL or you could provide some proper syntactically correct SQL to correct.

1st row+2nd line: Select concatenation 1
3rd row: (

shouldn't that be

1st row: Select concatenation 1
2nd line:  FROM
3rd row: (

and where's the order by row?


Can you try the 2nd form? It is probably easier to work with.
0
 

Author Comment

by:hermesalpha
ID: 33549333
It's version 2008.

SELECT COMPANY + ' ' + ADDRESS + ' ' + ZIP + 'have bought' CAST(COUNT(COMPANY.ID) AS varchar) + 'products.'  
FROM
(SORTER=1, COMPANY + ' ' + ADDRESS + ' ' + ZIP + 'have bought' CAST(COUNT(COMPANY.ID) AS varchar) + 'products.'  
FROM CUSTOMERS INNER JOIN SALES
ON COMPANY.ID = SALES.COMPANY_ID
WHERE COMPANY.ID=60
GROUP BY COMPANY, ADDRESS
UNION
SELECT SORTER=2, CONVERT(CHAR(10), SALESDATE, 120) + ' ' + PRODUCT
FROM CUSTOMERS INNER JOIN SALES
      ON COMPANY.ID = SALES.PERSON_ID
      INNER JOIN PRODUCT
      ON SALES.PRODUCT_ID = PRODUCT.ID
WHERE COMPANY.ID=60
GROUP BY SALESDATE, PRODUCT)SQ
ORDER BY SORTER, COMPANY + ' ' + ADDRESS + ' ' + ZIP + 'have bought' CAST(COUNT(COMPANY.ID) AS varchar) + 'products.'  

So there should be a total of what the customer/company has bought and this should be displayed on the first row: concatenation 1. The salesdates (as varchar) together with product name should be displayed on the rows below: concatenation 2.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33550869
Please find query below
SELECT COLUMN1

FROM

(

SELECT SORTER=1, COMPANY + ' ' + ADDRESS + ' ' + ZIP + 'have bought' + CAST(COUNT(COMPANY.ID) AS varchar) + 'products.' AS COLUMN1

FROM CUSTOMERS INNER JOIN SALES

ON COMPANY.ID = SALES.COMPANY_ID

WHERE COMPANY.ID=60

GROUP BY COMPANY, ADDRESS

UNION

SELECT SORTER=2, CONVERT(CHAR(10), SALESDATE, 120) + ' ' + PRODUCT

FROM CUSTOMERS INNER JOIN SALES

      ON COMPANY.ID = SALES.PERSON_ID

      INNER JOIN PRODUCT

      ON SALES.PRODUCT_ID = PRODUCT.ID

WHERE COMPANY.ID=60

GROUP BY SALESDATE, PRODUCT)SQ

ORDER BY SORTER, COLUMN1

Open in new window

0
 

Author Comment

by:hermesalpha
ID: 33555735
Thanks, it worked very well! Some things I wonder about:

SORTER=1, is sorter a function in T-SQL?

And is the point with putting the whole body within parenthesis that of separating the result sets of each select part? Without the parenthesis, the two select parts would have been treated as one results set, and ordered with numbers before letters, right?
0
 

Author Comment

by:hermesalpha
ID: 33555763
"Select Column1 From (Select Sorter=1...   : I just wonder why writing "From"? Usually, I use the From Tablename, what does this "From" mean? From the results set within parenthesis?

On the last but one line, "SQ": Is this a function? What is the meaning?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33555822
"Sorter" is just a name I gave to the extra column added to each part of the union.

Say top part returns

Bob
John
Jim

Bottom part returns

123
456

A normal union produces

Bob
John
Jim
123
456
(in no specific order) until you order it.  When ordered as text, numbers always go to the top.

What I did is in each part of the union, I added a column and made the value (fixed) 1 or 2.
Top part produces

1, Bob
1, John
1, Jim

Bottom part produces

2, 123
2, 456

Now that combines into

1, Bob
1, John
1, Jim
2, 123
2, 456

I can now sort on the first column (because it exists in the result data), but it does not need to be selected in the SELECT list.
0
 

Author Comment

by:hermesalpha
ID: 33555953
Ok, got it, so Sorter is just an empty column with a value for sorting purposes. Could just have been any name on the column then.

What about "SQ" just after the closing parenthesis?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33555994
SQ is again, just another name.  It is an alias given to the subquery, which is required by SQL Server since the query is not an actual table.  It can be used to reference the columns like SQ.Sorter.
0
 

Author Comment

by:hermesalpha
ID: 33556623
So I can't do without an alias for the subquery? And I could as well have named it Subquery?

Why don't I use " ) AS SQ"? Like I do when I assign an alias to a column?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33556664
For the same reason.

Select Column as Alias   ("as" is optional)
From Table as Alias ("as" is optional)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33558600
Yes you could have done "as SQ" but aliases on derived tables are not optional.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

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

18 Experts available now in Live!

Get 1:1 Help Now