CODEWITHSUNDEEP
sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

Results order with union query

I have a union query that's getting the count from two different populations. How can I force the results to be returned in the order they're written in the query and not in ascending/descending order?

select count(datediff(yyyy,dob,admitdate) as counts
from myTable
where condition
union
select count(datediff(yyyy,dob,admitdate) as counts
from myTable
where condition

I'd like for the first result to always be returned as the first row. Is this possible?

Upvotes: 3

Views: 1486

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753585

You have to specify the order with an ORDER BY clause. In your example, you might do:

SELECT 1 AS seq, COUNT(datediff(yyyy,dob,admitdate) as counts
  FROM myTable
 WHERE ...condition-1...
UNION
SELECT 2 AS seq, COUNT(datediff(yyyy,dob,admitdate) as counts
  FROM myTable
 WHERE ...condition-2...
 ORDER BY seq

Upvotes: 6

Gratzy
Gratzy

Reputation: 9389

select 1, count(datediff(yyyy,dob,admitdate) as counts
from myTable
where condition
union
select 2, count(datediff(yyyy,dob,admitdate) as counts
from myTable
where condition
order by 1

Upvotes: 1

Related Questions