Link to home
Start Free TrialLog in
Avatar of hermesalpha
hermesalphaFlag for Paraguay

asked on

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

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?
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Any sample rows from each to work with?
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
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
Avatar of hermesalpha

ASKER

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)
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
You can still use the code as shown - it just adds a sorter column to each part of the union.
Yup.  I missed that you had done that in your last post.  My bad CW!
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?
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

Here

Just to show i use ColumnA in order condition. :-)
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];




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,...

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.
Author,

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

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), ...
 
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
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.
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
<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?
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.
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.
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 ')'.
Line 19: it doesn't accept SQ after the parenthesis
Line 4: it doesn't accept (Sorter=1... either
1st row+2nd line: Select concatenation 1
3rd row: (
4th row: Sorter=1, concatenation 1

19th row: Group by.....)SQ  
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
"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?
"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.
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?
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.
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?
For the same reason.

Select Column as Alias   ("as" is optional)
From Table as Alias ("as" is optional)
Yes you could have done "as SQ" but aliases on derived tables are not optional.