A
A
Alexey Lebedev2014-07-27 19:16:59
SQL
Alexey Lebedev, 2014-07-27 19:16:59

How to sort a query using UNION?

SELECT TOP 50 id, fid, uid, type, val, message FROM sw.dbo.logs WHERE [email protected] or ([email protected] and type=100) ORDER BY id DESC;

We have such a request. It runs for a long time.
There is a suggestion to break it down into:
SELECT TOP 50 id, fid, uid, type, val, message FROM sw.dbo.logs WHERE [email protected] ORDER BY id DESC;

SELECT TOP 50 id, fid, uid, type, val, message FROM sw.dbo.logs WHERE ([email protected] and type=100) ORDER BY id DESC;

These 2 questions run much faster.
This is a logical step, but Union does not allow you to use ORDER BY in one of the queries.
(SELECT TOP 50 id, fid, uid, type, val, message FROM sw.dbo.logs WHERE [email protected] ORDER BY id DESC)
UNION ALL
(SELECT TOP 50 id, fid, uid, type, val, message FROM sw.dbo.logs WHERE ([email protected] and type=100) ORDER BY id DESC) ORDER BY id DESC;

How can this problem be easily solved? I do not want to sort by id on the server, I want to solve this problem using SQL.
I am using MS SQL 2012.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
I
iamnothing, 2014-07-27
@swanrnd

SELECT * FROM (...your query...) ORDER BY desired_field.
Just do not know how with the performance in this case.
I'll ask just in case: is your indexing configured for your needs?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question