select query with union and using order by

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 2000 Forums

SQL Server Development (2000)

select query with union and using order by

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2005-11-17 : 15:28:43

I have the following union query and tried to include this in a stored procedure. But getting error with the order by clause.
How to sort the records of both types(both select queries) by days field asc, which will have number of days in it.

Select actionid,actiondescription,days
FROM ACTIONS
ORDER BY days ASC
Union
Select revid,revwdescription,days
FROM Reviewers
ORDER BY days ASC

thank you very much for the info.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-17 : 15:31:43

You can only have one ORDER BY:

Select actionid,actiondescription,days
FROM ACTIONS
Union
Select revid,revwdescription,days
FROM Reviewers
ORDER BY days ASC

CODO ERGO SUM

reddymade
Posting Yak Master

165 Posts

Posted - 2005-11-17 : 15:52:56

I am getting the data sorted by days the one with parenthesis is
(-days)
I see a problem with (30), whats wrong with the query Please. it is supposed to be at the second or (0) is supposed to be after all the negative numbers.

Select type,under,actiondescription,days
FROM ACTIONS
Union
Select type,under,revwdescription,days
FROM Reviewers
ORDER BY days ASC

result is appearing as follows:

Type Under Description Days
ACT CN uityiuy (0)
ACT CN dftg (114)
Rev SU Reviewer (155)
ACT RM 4445 (156)
ACT SU 55465 (157)
ACT CN fddg (30)
ACT CO 4443 201
ACT RF 4477 209

Is there any thing wrong with the select query statements.
Thank you very much for the information.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-17 : 17:46:56

Do you mean that the Days column is actually stored as a varchar in this format? '(0)' , '(157)' , '(30)'

I won't go into all the reasons why this is a bad way to store data, except to say that the problem you are having is one of them.

The "ORDER BY days ASC" is working exactly the way it should, because it sorts VARCHAR data by the first character, then the second character, and so on. '(0)' is before '(114)' and '(157)' is before '(30)'.

If you want to sort them like numbers, you will have to convert the value of the Days column to numbers in your ORDER BY clause.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources