question

Barbara-3136 avatar image
0 Votes"
Barbara-3136 asked EchoLiu-msft commented

group and order with union

I'm trying to join these two queries and I don't know why it doesn't work.

(select coluna1, coluna2, coluna3, coluna4,coluna5 as produto, sum(coluna6) coluna6, min(coluna7) as coluna7, max(coluna8) as coluna8, coluna9, coluna10
from view
where coluna5 in ('prod1',
'prod2',
'prod3')

and coluna1 is not null
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10
order by coluna1, coluna2, coluna4,coluna5, min(coluna7))


UNION

(select coluna1, coluna2, coluna3, coluna4,coluna5 as produto, max(coluna6) coluna6, min(coluna7nova) as coluna7, max(coluna8nova) as coluna8, coluna9, coluna10
from view
where coluna5 in ('prod4',
'prod5',
'prod6')
and coluna1 is not null
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10
order by coluna1, coluna2, coluna4,coluna5, min(coluna7nova))

sql-server-generalsql-server-transact-sqlazure-sql-databasesql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Next time you ask a question do not only say "does not work", but explain what you mean by that. Particularly, include any error message you get.

As it happens, I can see the error directly this time. In an UNION query, the ORDER BY clause applies to the full UNION query; you cannot have ORDER BY clauses for each query. This might be query you want:

select coluna1, coluna2, coluna3, coluna4,coluna5 as produto, sum(coluna6) coluna6, min(coluna7) as coluna7, max(coluna8) as coluna8, coluna9, coluna10
from view
where coluna5 in ('prod1',
'prod2',
'prod3')
and coluna1 is not null
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10
UNION ALL
select coluna1, coluna2, coluna3, coluna4,coluna5 as produto, max(coluna6) coluna6, min(coluna7nova) as coluna7, max(coluna8nova) as coluna8, coluna9, coluna10
from view
where coluna5 in ('prod4',
'prod5',
'prod6')
and coluna1 is not null
group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10
order by coluna1, coluna2, coluna4,coluna5, coluna7

I changed UNION to UNION ALL, because that is what want most of the time. UNION remove duplicates which takes resources. And you may want to retain the duplicates.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Barbara-3136

Welcome to the microsoft tsql Q&A forum!

Or:

 ;With cte
 as(select coluna1, coluna2, coluna3, 
 coluna4,coluna5 as produto, 
 sum(coluna6) coluna6, min(coluna7) as coluna7,
 max(coluna8) as coluna8, coluna9, coluna10
 from view
 where coluna5 in ('prod1','prod2','prod3')
 and coluna1 is not null
 group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10
 UNION
 select coluna1, coluna2, coluna3,
 coluna4,coluna5 as produto, 
 max(coluna6) coluna6, min(coluna7nova) as coluna7,
 max(coluna8nova) as coluna8, coluna9, coluna10
 from view
 where coluna5 in ('prod4','prod5','prod6')
 and coluna1 is not null
 group by coluna1, coluna2, coluna3, coluna4,coluna5, coluna9, coluna10)
    
 select * from cte
 order by coluna1, coluna2, coluna4,coluna5,coluna7

For details, please refer to:
Using UNION of two SELECT statements with ORDER BY


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.