sql-server


Two sorted subqueries into one result


So I have two subqueries that return the same columns from the same table
Query #1:
SELECT E.Id,E.Title,E.LocationId,P.LocationId,E.DepartmentId,P.DepartmentId,E.DateCreated,E.IsActive,E.IsHotJob,E.RequisitionId,E.RequisitionIdString,E.RewardSettingId,E.EmploymentOpportunityStatusId
FROM EmploymentOpportunities E, Profiles P
WHERE E.EmploymentOpportunityStatusId = 9 AND E.IsActive = 1 AND E.IsHotjob = 1
AND P.Id = 'C5F07EBB-CE81-4133-A462-241A5F84D418' AND (P.DepartmentId != E.DepartmentId AND P.LocationId != E.LocationId)
ORDER BY E.DateCreated DESC
Query #2:
SELECT E.Id,E.Title,E.LocationId,P.LocationId,E.DepartmentId,P.DepartmentId,E.DateCreated,E.IsActive,E.IsHotJob,E.RequisitionId,E.RequisitionIdString,E.RewardSettingId,E.EmploymentOpportunityStatusId
FROM EmploymentOpportunities E, Profiles P
WHERE E.EmploymentOpportunityStatusId = 9 AND E.IsActive = 1 AND E.IsHotjob = 0 AND
P.Id = 'C5F07EBB-CE81-4133-A462-241A5F84D418' AND (P.DepartmentId = E.DepartmentId OR P.LocationId = E.LocationId)
ORDER BY E.DateCreated DESC
I want this two queries combines into one but preserve the order they have, so somehow stack Query #1 onto Query #2.
Is this possible?
SELECT 1 SetNumber, E.Id,E.Title,E.LocationId,P.LocationId,E.DepartmentId,
P.DepartmentId,E.DateCreated,E.IsActive,E.IsHotJob,E.RequisitionId,
E.RequisitionIdString,E.RewardSettingId,E.EmploymentOpportunityStatusId
FROM EmploymentOpportunities E, Profiles P
WHERE E.EmploymentOpportunityStatusId = 9 AND E.IsActive = 1
AND E.IsHotjob = 1 P.Id = 'C5F07EBB-CE81-4133-A462-241A5F84D418'
AND (P.DepartmentId != E.DepartmentId AND P.LocationId != E.LocationId)
union all
SELECT 2, E.Id,E.Title,E.LocationId,P.LocationId,E.DepartmentId,P.DepartmentId,
E.DateCreated,E.IsActive,E.IsHotJob,E.RequisitionId,E.RequisitionIdString,
E.RewardSettingId,E.EmploymentOpportunityStatusId
FROM EmploymentOpportunities E, Profiles P
WHERE E.EmploymentOpportunityStatusId = 9 AND E.IsActive = 1
AND E.IsHotjob = 0 AND P.Id = 'C5F07EBB-CE81-4133-A462-241A5F84D418'
AND (P.DepartmentId = E.DepartmentId OR P.LocationId = E.LocationId)
ORDER BY SetNumber, DateCreated desc
Just use UNION ALL
SELECT * FROM
(
Your Query1
UNION ALL // do not use order by here
Your Query2
) AS someName
ORDER BY yourColumn DESC
You can also use UNION - but it will filter out duplicates if any.

Related Links

when i exec select on linux with odbc, hang on '__select_nocancel'
The system cannot find the file specified on Production server
An Error when trying to connect to SQL Server
Informatica powercenter
Temporary table not created from dynamic query execution
How to process the data in the table, which is frequently inserted
Combine two columns and input the result in a different columns using SQL server
SQL Query to accept time difference
How to change my T-SQL query to overwrite a csv file rather than append data to it?
How to join multiple columns with only one foreign key using linq
Deleting millions on records while using log shipping - SQL Server
Restricting decimal places [duplicate]
Powershell T-SQL Update Statement Error in Function
How to close if case in SQL
Set RESTRICTED_USER on Azure SQL database
How to use highlight index parameter on ssrs

Categories

HOME
caching
dynamics-crm
winapi
drupal-7
cpu-architecture
vs-team-services
amazon-product-api
rfc
ios10
phaser-framework
histogrammar
devstack
ng-admin
hosts
wicket
image-recognition
public-key-encryption
uiview
rebol2
riak
medium-editor
r-lavaan
alfresco-share
ng-show
jogl
kitematic
rworldmap
auditing
geopandas
gzip
oracle-xml-db
freebase
pljson
rhino
von-neumann
sidr
readfile
poco-libraries
code-behind
productivity
html-agility-pack
upsert
z3py
bids
amazon-clouddrive
lirc
number-theory
drawingarea
sgmlreader
livescribe
execl
redux-router
pdfkit
createobject
barcode-printing
pspice
excon
database-backups
elastix
asp.net-mvc-partialview
inet
fortran90
application-loader
kendo-menu
retro-computing
android-snackbar
achartengine
calibration
rx-groovy
coypu
apache-mina
ember-cli-addons
robocode
sqoop2
python-ggplot
inputaccessoryview
moai
belongs-to
sortable
spdy
colt
dbsetup
visualstatemanager
mbox
cling
javascriptmvc
windows-update
localtime
cgimageref
adomd.net
sharpmap
xml-libxml
crocodoc
socketstream
mvccontrib
web-safe-fonts
moq-3
jpf
point-sprites
separation-of-concerns
hinstance
mysql-error-1045
ajax.beginform
scalaxb
phpcrawl
symbol-server
rijndael
email-spec
out-of-browser
rtsp-client
ajaxpro

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App