Join two queries and retain order

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join two queries and retain order

Author

Topic

jdbargas
Starting Member

1 Post

Posted - 2008-08-04 : 17:58:30

Hi all,

I have two queries that return results that i need to be able to put into one query.

SELECT Id, TermDesc
FROM Terms
WHERE (Id LIKE '%/%') AND (TermDesc NOT LIKE '%Nursing%')
ORDER BY TermStartDate

This returns these values:

Fall 2007
Winter 2008
Spring 2008
Summer 2008
Fall 2008
Winter 2009
Spring 2009
Summer 2009

The next query:

SELECT Id, TermDesc
FROM Terms
WHERE (Id NOT LIKE '%/%') OR
(TermDesc LIKE '%Nursing%')
ORDER BY TermStartDate

Returns similar results, with a distint order

CAPE ECE #19 CLEARLAKE
CAPE BSM #31 NAPA
DCP ECE #20 NAPA
DCP BSM #32 NAPA
DCP ECE #21 SANTA ROSA

I need a query that can join these and retain their separate orders to end up with results like this:

Fall 2007
Winter 2008
Spring 2008
Summer 2008
Fall 2008
Winter 2009
Spring 2009
Summer 2009
CAPE ECE #19 CLEARLAKE
CAPE BSM #31 NAPA
DCP ECE #20 NAPA
DCP BSM #32 NAPA
DCP ECE #21 SANTA ROSA

Everything I've tried results in the orders getting mixed and I can't have that.

Any ideas would be great! Thanks in advance for the help!

-Jordan

singularity
Posting Yak Master

153 Posts

Posted - 2008-08-04 : 19:21:16

SELECT Id, TermDesc
FROM
(SELECT Id, TermDesc, TermStartDate, 1 as SortOrder
FROM Terms
WHERE (Id LIKE '%/%') AND (TermDesc NOT LIKE '%Nursing%')
UNION
SELECT Id, TermDesc, TermStartDate, 2 as SortOrder
FROM Terms
WHERE (Id NOT LIKE '%/%') OR
(TermDesc LIKE '%Nursing%')) a
ORDER BY SortOrder, TermStartdate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources