Union query with oder by date in sql server 2005

Question

0.00/5 (No votes)

See more:

Hi all,

Below is my query, which returns top 5 rows order by date

SQL

select top 5 sd.sl_sub as topic,em.name as CreatedBy,sd.approved_date,'topic'as type  ,'' as comment_desc ,sd.sl_type
from wockhardt_022.sl_details sd
inner join dbo.employee_mapping map on map.user_id=sd.created_by  and effective_to_date is null
inner join  employee_master em   on map.employee_id=em.employee_id
where sd.div_code=@div_code
and sd.approved_flag=1
and sd.created_date < @last_date
--and approved_date > getdate() - 10

union

select top 5 sd.sl_sub as topic,em.name as CreatedBy,sc.approve_date as approved_date,'comment' as type ,sc.comment_desc,sd.sl_type
from wockhardt_022.sl_comment sc
inner join sl_details sd on sd.sl_id=sc.sl_id
inner join dbo.employee_mapping map on map.user_id=sc.comment_by and effective_to_date is null
inner join dbo.employee_master em on em.employee_id=map.employee_id
where sd.div_code=@div_code
and sc.approve_flag=1
and sc.approve_date < @last_date
order by approved_date  desc

But above query doesnt give output order by date.
What is wrong in this query?

Posted 26-Apr-13 20:11pm

Sweetynewb

Add a Solution

3 solutions

Solution 1

Let's call ordering a function over a set: O(S). Let your two query results be A and B. Now, your union statement is C=A U O(B), and that won't be ordered. What you need is C=O(A U B): first union than sort.

SQL

SELECT * FROM
(
SELECT ... --first query
UNION
SELECT ... --second query
)
ORDER BY whatever

Posted 26-Apr-13 20:32pm

Zoltán Zörgő

Comments

Aarti Meswania 27-Apr-13 2:41am

5+! :)

Sweetynewb 27-Apr-13 2:51am

getting error-Incorrect syntax near the keyword 'order'.

Zoltán Zörgő 27-Apr-13 2:55am

I hope you have not used "whatever"! Karthik Harve gave to you the exact statement, I tried to give you the theory and the method... but as I see, you didn't get it... :(

Sweetynewb 27-Apr-13 3:03am

after applying alias it is running but still one comment is not displaying. If i run separately then it gives that row but after union not.
I will try for this. thank you

Zoltán Zörgő 27-Apr-13 3:18am

Interesting. If all column types are compatible and there is no duplication, you should get all. Just an idea: where you select a constant, or anything that's type or length is not always obvious, use CAST.

Solution 3

please check the data type of column approved_date.
It should be datetime type and not varchar.

Posted 26-Apr-13 20:37pm

Gopal Gupta

Comments

Sweetynewb 27-Apr-13 2:53am

it is datetime.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*Karthik Harve* · Accepted Answer · 2013-04-26T20:35:00

Hi,

The solution is very simple. In your first select statement, you are not ordering the based on the date, but in the Second query you are ordering by date, but when union the queries, you could not find the ordered rows. I suggest you to put order statement for the overall result set rather ordering individual queries.

try like below.

SQL

select A.topic, A.CreatedBy, A.approved_date, A.type, A.comment_desc, A.sl_type
from
(
select top 5 sd.sl_sub as topic,em.name as CreatedBy,sd.approved_date,'topic'as type  ,'' as comment_desc ,sd.sl_type
from wockhardt_022.sl_details sd
inner join dbo.employee_mapping map on map.user_id=sd.created_by  and effective_to_date is null
inner join  employee_master em   on map.employee_id=em.employee_id
where sd.div_code=@div_code
and sd.approved_flag=1
and sd.created_date < @last_date
--and approved_date > getdate() - 10

union
 
select top 5 sd.sl_sub as topic,em.name as CreatedBy,sc.approve_date as approved_date,'comment' as type ,sc.comment_desc,sd.sl_type
from wockhardt_022.sl_comment sc
inner join sl_details sd on sd.sl_id=sc.sl_id
inner join dbo.employee_mapping map on map.user_id=sc.comment_by and effective_to_date is null
inner join dbo.employee_master em on em.employee_id=map.employee_id
where sd.div_code=@div_code
and sc.approve_flag=1
and sc.approve_date < @last_date
) as A
order by A.approved_date desc   -- ordering by date to the resultant record set

hope it helps.