-1

I have this situation:

select name, 
       subject 
from Table_1 
where date > getdate()-1 
group by name, subject 
order by id desc
union
select name, 
       subject 
from table_2 
where name not like 'abc%'

Table_1 and table_2 has similar structure.

I need to order by in SET1 UNION SET 2

This is not allowed in sql server. says "ORDER BY items must appear in the select list". I dont understand why the problem is. I am selecting equal number of columns on both queries. only that I want the result set together. (on SQL Server 2017)

Anybody help!!

Thanks in advance.

Ilyes
  • 14,640
  • 4
  • 29
  • 55
dotnetAndSqlServer
  • 19
  • 1
  • 1
    the order by goes in the final select. If you want to apply a special rule to the first value, you can do that with conditional sorting – S3S Oct 31 '18 at 20:30

1 Answers1

1

Elaborating on my comment

select name, 
       subject 
from Table_1 
where date > getdate()-1 
--group by name, subject --this isn't needed
union
select name, 
       subject 
from table_2 
where name not like 'abc%'
order by <yourCol> desc --notice change here

And for a conditional order by, there are a few posts on that.

Also, you don't need the group by since union removes duplicates.

But, the error is clear that the column you want to order by must be contained in the select list...

If you want to keep the first set ordered before the second set, just use a static column....

select name, 
       subject,
       1 as Sort
from Table_1 
where date > getdate()-1 
--group by name, subject --this isn't needed
union
select name, 
       subject,
       2 as Sort 
from table_2 
where name not like 'abc%'
order by Sort asc--notice change here
S3S
  • 24,809
  • 5
  • 26
  • 45
  • 3
    How it is going to work? First part does not have id column – Lukasz Szozda Oct 31 '18 at 20:35
  • I didn't catch that @LukaszSzozda but edited answer. Nice catch – S3S Oct 31 '18 at 20:36
  • @scsimon, SQL allows me to order by on the whole set. But I want the order by to be applied on only set 1. I just want to add set 2 to the result set. Is there any way I can achieve this – dotnetAndSqlServer Nov 01 '18 at 15:24
  • You can - put a rownumber on each select in the right order, then sort by that after the union (as well as the Sort number above). But there is an issue - You want to group the first part by name and subject, then sort by ID - that doesn't work. If the same name and subject had ID's 10 and 20, which ID would you sort by? You need to figure out how you want the first part sorted before we can worry about the union – DancingFool Nov 02 '18 at 03:00